Return Top Product AND total revenue with one DAX measure

[dMac314]

Hello All,

I am new to DAX and PBI and here as well, this is actually my first question 🙂 so pardon me newbness. I am trying to fgure out a way to return the top product and total revenue (for that product) for the most current month with one measure.

Thus far I have the following: 

CurrentSum:= CALCULATE(SUM(Table[Payment]), FILTER(table, table[Payment Year] = YEAR(NOW()) && table[Payment Month] = MONTH(NOW())))



TopProduct:= IF(NOT(ISBLANK([CurrentSum])), IFERROR(CALCULATE(VALUES(table[Product]),TOPN(1, VALUES(table[Product]), [CurrentSum])), "Multiple Products - Tied"))



CurrentProductSum:=(CALCULATE([CurrentSum],TOPN(1, VALUES(table[Product]), [CurrentSum])))

These work just fine but I'd like to be able to return these 2 values (TopProduct and CurrentProductSum) withnone measure and possibly a few at a time e.g. top 10 etc. I can do this easily with page level filters but I'd like to be able to do it dynamically with DAX.

Thanks!!!

[v-yulgu-msft]

Hi @dMac314,

You can create a Rank measure based on total revenue of each product. Then, add this Rank measure to "visual level filters", set its value to 1 or less than 10.

Regards,

Yuliana Gu 

[dMac314]

Thanks for your response @v-yulgu-msft. I understand how to get it done this way but I am looking for a solution that would return everything at once so I wouldnt have to worry about setting any visual level filters.