How to filter a DAX calculation using a calculated measure

Anonymous — Wed, 02 Sep 2020 17:35:20 GMT

Hello,

My objected is two parts (1) Calculate a dynamic ranking for a basic matrix (2) Create individual measures that return the name and profit for a specific rank (e.g. rank 2).

(1) Created Dynamic ranking calculated measure with following equation:

Product Profit Rank = RANKX(ALLSELECTED(Orders[Product Name]),CALCULATE(SUM(Orders[Profit])),,DESC)

As you can see, it worked. And as I change the filters on the page, it dynamically adjusts.

(2) Attempted to create individual measures that highlight a specific rank, but am having issues. I would like a measure that returns the name of 'n' ranked product and a second that returns the profit of 'n' ranked product. Here are the DAX calculatons I think should work, but they are not. It seems that the filter portion is what is having issues, so just the first nonblank is returned or the profit for all products. Your help would be apprecaited. Thanks!

Rank 2 Product Name = CALCULATE(FIRSTNONBLANK(Orders[Product Name],1),FILTER(Orders, [Product Profit Rank] = 1))

Rank 2 Product Profit = CALCULATE(SUM(Orders[Profit),FILTER(Orders, [Product Profit Rank] = 1))

Re: How to filter a DAX calculation using a calculated measure

amitchandak — Wed, 02 Sep 2020 17:58:38 GMT

Anonymous , refer

https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

, refer my rank

City Rank = RANKX(all(Geography[City]),[Sales])

filters I tried

Rank Top 10= CALCULATE(if([City Rank]<=10,[Sales],BLANK()) ,VALUES(Geography[City]))
Rank Top 10 1 = sumx(VALUES(Geography[City]),if([City Rank]<=10,[Sales],BLANK()) )
Top 10 City Rank = CALCULATE([Sales],TOPN(10,all(Geography[City]),[Sales],DESC),VALUES(Geography[City Id]))
Rank 2nd top = sumx(filter(VALUES(Geography[City]),[City Rank]=2),[Sales] )

https://databear.com/power-bi-dax-topn-function/

topic How to filter a DAX calculation using a calculated measure in DAX Commands and Tips

How to filter a DAX calculation using a calculated measure

Re: How to filter a DAX calculation using a calculated measure