Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
WorkHard
Helper V
Helper V

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:

WorkHard_0-1620767988466.png

 

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

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:  

 

v-yalanwu-msft_0-1620989187598.pngv-yalanwu-msft_1-1620989187603.png

 

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.   

 

View solution in original post

7 REPLIES 7
v-yalanwu-msft
Community Support
Community Support

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:  

 

v-yalanwu-msft_0-1620989187598.pngv-yalanwu-msft_1-1620989187603.png

 

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
Super User
Super User

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.


Go to My LinkedIn Page


HashamNiaz
Solution Sage
Solution Sage

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
Resident Rockstar
Resident Rockstar

Try as:

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

ChrisMendoza_0-1620768743413.png

 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

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

Measure = CALCULATE(SUM(Data[Amount]),ALLEXCEPT(Data,Data[Event]))
I tested it with a slicer for Event.
Untitled.png

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.