Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Sum by Last Operation

Greetings all,

 

I'm new to Power BI/Dax in general so apologize if this might seems obvious but I couldn't manage to find a way to do it.

I have a scenario to Sum the Last Sequence of Each ID as below.

 

Grand Total = 5828 + 6071 = 11 899

 

to full fill the Above Requirement, I have created the below DAX, but it is giving only the Max Sequence Amount in total.

 

CALCULATE(SUMX('TABLE',[AMOUNT]),
FILTER(
ALL('TABLE'[Sequence]),
'TABLE'[Sequence] = MAX('TABLE'[Sequence]
)))

 

Result with Wrong Grand Total :

Can you please help with this? 

 

Thanks,

Praveen

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    Please try:

    Measure =
VAR _t =
    ADDCOLUMNS (
        SUMMARIZE ( 'Table', [ID], "Max Sequence", MAX ( 'Table'[Sequence] ) ),
        "Amount",
            LOOKUPVALUE (
                'Table'[Amount],
                'Table'[ID], [ID],
                'Table'[Sequence], [Max Sequence]
            )
    )
RETURN
    SUMX ( _t, [Amount] )

    Output:

     

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

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Try

    CALCULATE(SUMX(values('TABLE'[Sequence]),[AMOUNT]),
    FILTER(
    ALL('TABLE'[Sequence]),
    'TABLE'[Sequence] = MAX('TABLE'[Sequence]
    )))

     

     

    or

     

     

    CALCULATE(SUMX(values('TABLE'[Sequence]),[AMOUNT]),
    FILTER(
    ALLSELECTD('TABLE'[Sequence]),
    'TABLE'[Sequence] = MAX('TABLE'[Sequence]
    )))

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello Amit,

      Thanks for the prompt response , both are giving the same result which i have attached with question.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Same result  

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    Please try:

    Measure =
VAR _t =
    ADDCOLUMNS (
        SUMMARIZE ( 'Table', [ID], "Max Sequence", MAX ( 'Table'[Sequence] ) ),
        "Amount",
            LOOKUPVALUE (
                'Table'[Amount],
                'Table'[ID], [ID],
                'Table'[Sequence], [Max Sequence]
            )
    )
RETURN
    SUMX ( _t, [Amount] )

    Output:

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you  Eyelyn Qin