Forum Discussion

WorkHard's avatar
WorkHard
Helper V
5 years ago
Solved

Incorrect Total in Measure when rows are repeated (flat file)

In a measure, how do I calculate the totals for each EventId while also maintain a correct Grand Total? Currently, the grand total calculates all the rows like so:  
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    Hi WorkHard  ,  

     

    You could create a measure by the following formula: 

    Total Event Amount =
IF (
    ISFILTERED ( 'Table'[Amount] ),
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALL ( 'Table' ),
            [Event]
                = CALCULATE (
                    MAX ( 'Table'[Event] ),
                    FILTER ( 'Table', [Amount] IN ALLSELECTED ( 'Table'[Amount] ) )
                )
        )
    ),
    CALCULATE ( SUM ( 'Table'[Amount] ), ALLEXCEPT ( 'Table', 'Table'[Event] ) )
)

    If [Amount] as a slicer ,The final output is shown below:  

     

     

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.   

     

Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

Hi, WorkHard 

Please try the below.

 

Total Event Amount =
IF (
ISFILTERED ( 'Table'[Event] ),
CALCULATE ( SUM ( 'Table'[Amount] ), ALLEXCEPT ( 'Table', 'Table'[Event] ) ),
SUM ( 'Table'[Amount] )
)

 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM