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.   

     

HashamNiaz's avatar
HashamNiaz
Solution Sage
5 years ago

Hi !

You can use the INSCOPE() function to get the desired output;

 

Total = IF(ISINSCOPE(YourTable[EventID]), [Total Event Amount], [Amount])

 

Replace YourTable with correct table name.

 

Regards,

Hasham