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.   

     

7 Replies

  • ChrisMendoza's avatar
    ChrisMendoza
    Resident Rockstar
    5 years ago

    Try as:

    Measure = 
CALCULATE(
    SUM(TableName[Amount]),
    ALLEXCEPT(TableName,TableName[Event])
)

     

    • WorkHard's avatar
      WorkHard
      Helper V
      5 years ago

      Hi ChrisMendoza ,
      This doesn't work if I use slicers to further slice through the data.

      It shows the same total all the time.

      • ChrisMendoza's avatar
        ChrisMendoza
        Resident Rockstar
        5 years ago

        WorkHard - I don't know what you mean. Can you provide a sample of what you are expecting and what you are slicing by?

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    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

  • 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