Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Solved! Go to 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.
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.
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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
Try as:
Measure =
CALCULATE(
SUM(TableName[Amount]),
ALLEXCEPT(TableName,TableName[Event])
)
Proud to be a Super User!
Hi @ChrisMendoza ,
This doesn't work if I use slicers to further slice through the data.
It shows the same total all the time.
Hi,
The solution provided by @ChrisMendoza works fine for me. This is the measure
@WorkHard - I don't know what you mean. Can you provide a sample of what you are expecting and what you are slicing by?
Proud to be a Super User!
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |