Forum Discussion

topazz11's avatar
topazz11
Icon for Helper III rankHelper III
7 years ago
Solved

Maximum value and total by group

@Ashish_Mathur  helped me this yesterday. it works great but I want to sum the max_amt column by group.

Currently it picks up larger value between amt1 and amt2.

as an example below, it should be 1600, not 1550.

PBI file is here.  PBIX file

 
 Product IDLine NoAmt1 Amt2Max_Amt
 PP89    1   250     100    250
       2   500     550    550
      3   800     500    800
    Total 1,550    1,150  1,600

Thank you,

Need Help
  • v-xicai's avatar
    v-xicai
    7 years ago

    Hi topazz11 ,

     

    You can create measure like DAX below.

     

    Max_Amt = 
    VAR Amount1 = if(HASONEVALUE(Append1[Attribute]),[Total],MAXX(VALUES(Append1[Attribute]),[Total]))
VAR _table = SUMMARIZE(Append1, Append1[Area],Append1[Prod ID],Append1[Line No],"_Value",[Amount1])
RETURN
IF(HASONEVALUE(Append1[Line No]),[Amount1],SUMX(_table,[_Value]))

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi topazz11 ,

     

    You can create measure like DAX below.

     

    Max_Amt = 
    VAR Amount1 = if(HASONEVALUE(Append1[Attribute]),[Total],MAXX(VALUES(Append1[Attribute]),[Total]))
VAR _table = SUMMARIZE(Append1, Append1[Area],Append1[Prod ID],Append1[Line No],"_Value",[Amount1])
RETURN
IF(HASONEVALUE(Append1[Line No]),[Amount1],SUMX(_table,[_Value]))

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.