Forum Discussion
Return largest category based on a sum
Hi,
These measures work
Total amount = SUM(Data[Amount])
Top product = FIRSTNONBLANK ( TOPN ( 1, VALUES ( Data[Product] ), [Total amount] ), 1 )
Hope this helps.
Super UserAnonymous , Based on what I got
Create a rank Measure and filter Rank =1
rank measure =
Rankx(all(Table[product]), calculate(Sum(Table[Amount])))
Thanks amitchandak, formula seems to work.
However, because I use the names ("Person 1", "Person 2", etc.) as the row filter in the Pivot Table, only one product is currently returned as value. So, the rank function simply returns "1" for all employees, and hence it is not possible to filter.
Any alternative suggestions?
- amitchandak
Anonymous , this how-to measure Rank work. The one I suggested will create a single rank if used with the product and if used with the employee you will get inside the employee.
Try like
RANKX (
FILTER(
ALL( /////////Or allselected
'Table'[employee],
'Table'[product ]
),
'Table'[employee] = MAX('Table'[employee])
),
CALCULATE(SUM('Table'[Amount]))
)For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415Thanks amitchandak, I will have a look at the links.
Not sure how the proposed measure works, since it requires taking MAX of employee, which is a string.