Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dMac314
Frequent Visitor

Return Top Product AND total revenue with one DAX measure

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

 

2 REPLIES 2
v-yulgu-msft
Employee
Employee

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 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.