Forum Discussion
Rankx by a column
Hello, I am still very new to Power BI and I am having trouble understanding Rankx.
I would really appreciate if you could please help me with this 😄
Currently, I have 4 measures, Item Type, Item Class, Brand and Dollar
Item Type: A1,A2,A3,A4,A5
Item Class: B11,B12,B13,B14,B15,B16,B17,B18,B19,B21,B22,B23,B24,B25,B26,B31,B32,B33,B41,B51,B52
Brand: C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12
Think of Item Type as "Laptop", "CellPhone" etc...
Item Class as "13' Laptop", "15' Laptop", "Flip phone", "Smart phone" etc...
Brand as "Samsung", "Apple" etc....
So there is unique set of Item Class designated to each Item Type, but Brand does not have this limitation.
I am using Item Type in a slicer and trying to rank Item Class by the Dollar like this:
where Rank1=RANKX(ALL('Table'[Item Class]),calculate(sum([Dollar])),,DESC)
(Dollar is not a measure, it is a summary of the column)
Now, I am trying to add the Brand column and hope to have the same rank number matching to the Item Class.
Instead, I get this:
And what I need is :
I intend to make a treemap with this rank.
This is what I am getting:
But I would like to have the treemap to look like this instead:
(1st Dollar by Brand = calculate(sum('Table'[Dollar]),'Table'[Item Class]="B15") because "B15" is the Item Class where the Rank1=1)
And I would like to create this treemap for top 5 ranks (rank=1, rank=2, rank=3, etc...)
Though it is not included in this example, I am planning to use this logic in another dataset that will be filtered with multiple slicers. (Date, etc...)
Is there a way to achieve this using DAX?
Thank you!!
Here's the measure. I think it now works correctly in ALL CIRCUMSTANCES.
Ranking = IF( HASONEFILTER( 'Data'[Item Class] ), RANKX( ALLSELECTED( Data[Item Class] ), var __itemClass = Data[Item Class] return CALCULATE( [Dollar Amount], Data[Item Class] = __itemClass, ALLSELECTED( Data ) ), CALCULATE( [Dollar Amount], VALUES( Data[Item Class] ), ALLSELECTED( Data ) ), DESC, Dense ) )Best
D
14 Replies
Hello! So it says I cannot paste the data on this, so I let me provide a pivoted table of the data!
Item Type Item Class Brand Sum of Dollar A1 B11 C4 559638071 A1 B11 C5 37638 A1 B12 C2 8378024 A1 B12 C4 24458186 A1 B12 C5 515795839 A1 B13 C2 146905813 A1 B13 C4 86777553 A1 B14 C1 601528639 A1 B14 C3 698254 A1 B14 C5 4277261 A1 B15 C1 10731303201 A1 B15 C2 1856720 A1 B15 C3 87986539 A1 B15 C4 19217314 A1 B16 C2 1614411 A1 B16 C3 8555446 A1 B16 C4 51040 A1 B16 C5 8992 A1 B17 C2 1943911737 A1 B17 C3 44 A1 B17 C4 117482329 A1 B18 C3 4965049 A1 B18 C5 820918 A1 B19 C1 166831158 A1 B19 C2 1 A1 B19 C3 5870631 A1 B19 C4 792756 A2 B21 C1 161379075 A2 B21 C2 85707959 A2 B21 C3 5911 A2 B21 C4 168073 A2 B21 C5 843221864 A2 B21 C6 25931941 A2 B21 C7 47 A2 B22 C1 19 A2 B22 C3 31081 A2 B22 C4 43 A2 B22 C6 66203708 A2 B22 C7 36329292 A2 B23 C1 82633 A2 B23 C2 62056222 A2 B23 C3 4601008927 A2 B23 C4 483 A2 B23 C5 506 A2 B23 C6 520 A2 B24 C2 297645 A2 B24 C3 42232079 A2 B24 C4 2326158588 A2 B24 C5 662131297 A2 B24 C6 1928812 A2 B24 C7 6621 A2 B25 C1 96983088 A2 B25 C3 496431282 A2 B25 C4 1608955018 A2 B25 C5 16766576 A2 B25 C6 84 A2 B26 C1 77201633 A2 B26 C4 417 A2 B26 C6 6027347783 A2 B26 C7 54460600 A3 B31 C10 16538019041 A3 B31 C11 7798694 A3 B31 C12 10 A3 B31 C2 3037667 A3 B31 C3 240519145 A3 B31 C4 4600198227 A3 B31 C5 21786 A3 B31 C6 86 A3 B31 C7 2266 A3 B31 C8 3537 A3 B31 C9 37538535 A3 B32 C1 472711 A3 B32 C10 9874115 A3 B32 C12 5211176855 A3 B32 C2 32686956 A3 B32 C6 3341362153 A3 B32 C7 3337361 A3 B32 C8 7198223 A3 B32 C9 623 A3 B33 C1 905 A3 B33 C2 712516648 A3 B33 C3 272 A3 B33 C4 1941400395 A3 B33 C5 765114231 A3 B33 C6 85670076 A3 B33 C7 106555 A3 B33 C8 221295 A3 B33 C9 546183285 A4 B41 C10 12186138703 A4 B41 C11 414246 A4 B41 C3 9515872856 A4 B41 C4 3817771326 A4 B41 C5 59877132 A4 B41 C6 18269309391 A4 B41 C7 86929608 A4 B41 C8 90886321 A4 B41 C9 6820892674 A5 B51 C3 47309110 A5 B51 C4 9540363 A5 B51 C5 12040613221 A5 B51 C6 213316664 A5 B51 C7 9693108402 A5 B52 C3 13778740590 A5 B52 C4 47283886 A5 B52 C5 1162957495 A5 B52 C6 5837010088 A5 B52 C7 6116792986