Need help on power BI Multi level ranking

[Anonymous]

Hi Everyone!

Need your help on Power BI Rank Dax function

We have the data like below 

Table Visual

Brand	Product	Sales	Rank
A	C	200	1
A	D	190	2
A	E	180	3
A	A	170	4
A	F	160	5
B	Z	900	1
B	I	800	2
B	Q	700	3
B	N	400	4
B	B	300	5
B	S	200	6
C	A	900	1
C	B	300	2
C	Q	150	3
C	R	100	4
C	C	90	5
C	G	80	6

sales and rank are measure

sales is a dynamic measure , the value of the sales will change based on Time Period (YTD,MTD,QTR)

Sales =

var SelectedMeasure =
SELECTEDVALUE('Time Period'[Time Period],"YTD")

var DynamicMeasureValue =
SWITCH(
TRUE(),
SelectedMeasure = "YTD", [YTD],// gives you the YTD sales
SelectedMeasure = "QTD", [QTD],// gives you the QTD sales
SelectedMeasure = "MTD", [MTD],// gives you the MTD sales
BLANK()
)
RETURN
DynamicMeasureValue


Time Period is a disconnected TABLE. PFB

Time Period	Time Key
MTD	1
QTD	2
YTD	3

created a slicer out of this table , which will give sales based on the slicer selection(YTD,QTD,MTD)

Rank = RANKX(
ALL('TABLE'[Product]),
[Sales]
)

I Just need to apply a filter where Product = Brand
want the output like below

Brand	Product	Sales	Rank
A	A	170	4
B	B	300	5
C	C	90	5

I have created a flag calclated column to achieve that
flag = IF(
'TABLE'[Brand]='TABLE'[Product],
1,
0
)

I have added this as a visual level filter and selected 1
getting the output like this

Brand	Product	Sales	Rank
A	A	170	1
B	B	300	1
C	C	90	1

Please help how can we get the desired output like below

Brand	Product	Sales	Rank
A	A	170	4
B	B	300	5
C	C	90	5

Thanks,

Ananth

[v-cazheng-msft · Accepted solution]

Hi @Anonymous 

Please change the flag Calculate column to a Measure. Then, the result will look like this.

Best Regards,

Community Support Team _ Caiyun

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

[Anonymous]

HI !,

Thanks for your response.

Actually sales is a dynamic measure , the value of the sales will change based on Time Period (YTD,MTD,QTR)

Sales =

var SelectedMeasure =
SELECTEDVALUE('Time Period'[Time Period],"YTD")

var DynamicMeasureValue =
SWITCH(
TRUE(),
SelectedMeasure = "YTD", [YTD],// gives you the YTD sales
SelectedMeasure = "QTR", [QTD],// gives you the QTD sales
SelectedMeasure = "MTD", [MTD],// gives you the MTD sales
BLANK()
)
RETURN
DynamicMeasureValue


Time Period is a disconnected TABLE. PFB

Time Period	Time Key
MTD	1
QTD	2
YTD	3

created a slicer out of this table , which will give sales based on the slicer selection(YTD,QTD,MTD)

Thanks,

Ananth

[AUaero]

I've recreated your data in a test file on my end.  The only thing that I've done that you didn't explicitly say you did was create a measure for sales:

$ Sales = SUM(SampleData[Sales])

The rank measure is:

Rank = 
RANKX(
    ALL(SampleData[Product]),
    SampleData[$ Sales]
)

 and a calculated column for Product = Brand:

ProductBrandMatchFlag = 
IF(
    SampleData[Product] = SampleData[Brand],
    1,
    0
)

I created a table visualization and dropped the ProductBrandMatchFlag filter onto it.  When I filter by ProductBrandMatchFlag = 1 I get your desired result: