Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Return largest category based on a sum

Hi there,   I am looking for a (hopefully) simple DAX measure to solve the following problem.   I have a range consisting of a number of employees, a chosen product, and their savings in each pro...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    These measures work

    Total amount = SUM(Data[Amount])

    Top product = FIRSTNONBLANK ( TOPN ( 1, VALUES ( Data[Product] ), [Total amount] ), 1 )

    Hope this helps.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

     

FrankAT's avatar
FrankAT
Icon for Community Champion rankCommunity Champion
5 years ago

Hi Anonymous 

take a look at the following solution:

 

 

 

Max Amount = 
Var _Rank = RANKX(ALL('Table'[Product]),[Sum of Amount],,DESC)
RETURN
    IF(_Rank = 1, [Sum of Amount], BLANK())

 

 

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks FrankAT, worked a charm in PowerBI.

     

    However, when trying to convert the formula to Excel PowerPivot, I cannot seem to make it work.

     

    I believe the problem is that Excel will not let you return a text string in a pivot table (unless it is used with e.g. FIRSTNONBLANK).

     

    How do I adjust the formula, so I get the formula to return the correct Product as a text string (I do not need to show the actual amounts in the table, just the product)?

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous,

      Excel and power bi use different logic and structure to store data tables.

      Perhaps you can try to convert your table to the query table and use the 'M query' to create a reference query to summarize raw table records.

      let
    Source = RawTable,//change to your query table name
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Product", type text}, {"Amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name", "Product"}, {{"Total", each List.Sum([Amount]), type nullable number}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Name"}, {{"Count", each Table.LastN(_, 1), type table}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows1", "Count", {"Product", "Total"}, {"Product", "Total"})
in
    #"Expanded Count"


      Regards,

      Xiaoxin Sheng