Rankx by a column

[Anonymous]

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:

where

1st Dollar by Brand Test=calculate(sum('Table'[Dollar]),filter(allselected('Table'[Item Class]),[Rank1]=1))

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!!

[harshnathani]

HI @Anonymous ,

Please share your sample data in text format.

Copy in excel sheet and paste it.

Regards,

HN

[Anonymous]

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

[Anonymous]

[Ranking] =
IF( HASONEFILTER( 'Table'[Item Class] ),
	CALCULATE(
		RANKX(
			VALUES( 'Table'[Item Class] ),
			CALCULATE( SUM('Table'[Dollar] ) ),
			,
			DESC
		),	
		ALLSELECTED( 'Table' )
	)
)

Best

D

[Anonymous]

Hello @Anonymous !

Thank you so much for your reply. I really appreciate it.

However, your DAX has given me a column of 1's.

I figured out the DAX that I wanted, which is:

RANKX(ALL('Table'[Item Class]),calculate(sum([Dollar]),all('Table'[Brand])),,DESC)

got the hint from https://community.powerbi.com/t5/Desktop/Rank-by-multiple-values/td-p/636102

Thank you! Have a great day 🙂 

[Anonymous]

Well, sadly, your measure will be misbehaving as soon as you add other columns to your table and start slicing by them... So your solution is only partial.

Best
D

[Anonymous]

Hello @Anonymous !

You are right.

I thought it was correct because it worked on my example data.

But when I tried to apply the logic into my real data, and it seems like some ranking are being skipped.

Without the Brand, the DAX will give me the correct Rank, but with the Brand, it skips some Rank # (no #1, no #4, etc...)

However, your's is still showing a column of 1's, both my real data and the example data.

If you don't mind, could you please help me furthur on this problem?

Thank you in advance!

[stevedep]

Hi,

Regarding the skipping of ranks, you should probably set the ties parameter to 'dense'.

Please mark as solution if this solves it for you.

Kind regards,

Steve

[Anonymous]

I know mine is wrong. I just typed it without any data to test it on. That's why. To write the measure you're requesting is not an easy feat. I'll try to figure this out but can't guarantee...

Best
D

[Anonymous]

Hello @Anonymous 

Thank you.

I will continue working on it as well!

Is mine working funky because of 'All' statement?

Should I have a 'Allselected' statement somewhere in the DAX?

[Anonymous]

Removing the gaps is easy:

Your Ranking = 
RANKX(
    ALLSELECTED( 'Table'[Item Class] ),
    CALCULATE(
        SUM( 'Table'[Dollar] ),
        ALL('Table'[Brand])
    ),,
    DESC,
    Dense
)

But this still is not correct under all conditions, especially if you add new columns and start slicing by them.

Best

D

[Anonymous · Accepted solution]

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

[Anonymous]

By the way, in the formula above [Dollar Amount] is SUM( Data[Dollar] ).

Best
D

[Anonymous]

Sadly, it's not that easy. The problem is this. You have filters on your slicers and in the table itself (each row in the table filters data, of course). ALLSELECTED works only on individual columns or the whole table. Here the problem is that we have to be in a position to somehow discover that some filters are coming from slicers and some from the table itself. This is the a REALLY HARD PART. I'm not even sure it can be done in full generality (I have not had to do it throughout the whole of my career as a DAX developer - 5 years).

It might well be that you'll have to stick to some partial versions working correctly only in certain circumstances 😞

Best
D

[Anonymous]

Hi @Anonymous 

Thank you so much for your assitance! 

I really appreciate your help. 😄