Forum Discussion
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:
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.
Try as:
Measure =
CALCULATE(
SUM(TableName[Amount]),
ALLEXCEPT(TableName,TableName[Event])
)
Hi ChrisMendoza ,
This doesn't work if I use slicers to further slice through the data.It shows the same total all the time.
WorkHard - I don't know what you mean. Can you provide a sample of what you are expecting and what you are slicing by?
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.
