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

[WorkHard]

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:

[v-yalanwu-msft · Accepted solution]

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]

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

[HashamNiaz]

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

[ChrisMendoza]

Try as:

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

[WorkHard]

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

It shows the same total all the time.

[Ashish_Mathur]

Hi,

The solution provided by @ChrisMendoza works fine for me.  This is the measure

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

I tested it with a slicer for Event.

[ChrisMendoza]

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