CODEWITHSUNDEEP
sql-serverpowerbidax
Yikes_H
Yikes_H

Reputation: 29

DAX Adding up ranks over another grouping

I have a table with data like this:

StoreID |  Week/End  | Sales
===========================
  1     |   9/2      |  10
  2     |   9/2      |  5
  3     |   9/2      |  3
  1     |   9/9      |  5
  2     |   9/9      |  2
  3     |   9/9      |  10

What I need to do is rank the sales by week, then add up the value of the ranking.

I tried a measure using RANKX like this:

=RANKX(ALL(Stores), SUMX(RELATEDTABLE(Sales), [Sales]))

Which will give me the following table:

StoreID |  Week/End  | Sales  | Rank
=====================================
  1     |   9/2      |  10    |   1
  2     |   9/2      |  5     |   2
  3     |   9/2      |  3     |   3
  1     |   9/9      |  5     |   2
  2     |   9/9      |  2     |   3
  3     |   9/9      |  10    |   1

What I need to do now is to add up the rank column by Store, so in the end I would get a table like this:

StoreID |  Overall Rank
=======================
  1     |   3
  2     |   5
  3     |   4

So store 1 would have a value of 3, because of the w/e 9/2 of 1 and a w/e 9/9 rank of 2. store 2 would have a overall rank of 5, because of the w/e 9/2 of 2 and a w/e 9/9 rank of 3, and so on.

I would try to do this in SQL, but unfortunately some of my base data is in excel and some of it is in SQL, so I need to do this in DAX or M.

Any ideas would be greatly appreciated!

Upvotes: 0

Views: 161

Answers (1)

Monika Kastner
Monika Kastner

Reputation: 128

as I understand you are using Power BI. If you are in the tables menu (second one) I would create new table with column name StoreID and written variables 1, 2 and 3.

Then in relations menu (third) connect those column by StoreID. When you come back to second table in tables menu there would be an option to add calculated column with:

Overall_Rank= CALCULATE(SUM(first_table[Rank]))

As they are connected it will recognise matching StoreID so no need for any other filter.

Upvotes: 1

Related Questions