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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
mojo_flex
New Member

Conditional measure to avoid sum duplicates

Hi,
I would like some help creating a measure that avoids summing duplicates and incorporates some conditional logic.

 

I'm very new to DAX and so it may be that what I am attempting is impossible or better achieved in an alternate way. Advice, greatly appreciated!

 

Given something similar to the following table:

Annotation 2023-11-29 154946.png

I'd like to create a measure that avoids duplicates across all columns for CAT = A but only avoids duplicates across CAT and VALUE for CAT = B or C.
Essentially the measure should sum the following:
Annotation1 2023-11-29 163040.png

 

The following code successfully avoids duplicates across all columns but I am unsure how to ammend it to achieve the conditional logic described.

 

[measure] = 
SUMX(
    SUMMARIZE(
        T,
        T[DATE],
        T[CAT]
        T[VALUE]
    ),
    T[VALUE]
)

 

The above essentially achieves:

Annotation2 2023-11-29 163115.png

However for CAT B and CAT C I guess the following should be applied:

[measure] = 
SUMX(
    SUMMARIZE(
        T,
        T[CAT],
        T[VALUE]
    ),
    T[VALUE]
)

Many thanks in advance!

 

1 ACCEPTED SOLUTION
ERD
Community Champion
Community Champion

@mojo_flex , one of the ways to achieve this is to use the virtual tables you've created:

conditional_sum =
VAR t_A = SUMMARIZE ( T, T[DATE], T[CAT], T[Value] )
VAR t_BC = SUMMARIZE ( T, T[CAT], T[VALUE] )
VAR current_cat = SELECTEDVALUE ( T[Cat] )
RETURN
    IF ( current_cat = "A", SUMX ( t_A, [Value] ), SUMX ( t_BC, [Value] ) )

Is this what you want to achieve?

ERD_0-1701278687587.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

View solution in original post

4 REPLIES 4
ERD
Community Champion
Community Champion

@mojo_flex , can you, please, show the result you want to achieve?

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

@ERD further explanation added...

ERD
Community Champion
Community Champion

@mojo_flex , one of the ways to achieve this is to use the virtual tables you've created:

conditional_sum =
VAR t_A = SUMMARIZE ( T, T[DATE], T[CAT], T[Value] )
VAR t_BC = SUMMARIZE ( T, T[CAT], T[VALUE] )
VAR current_cat = SELECTEDVALUE ( T[Cat] )
RETURN
    IF ( current_cat = "A", SUMX ( t_A, [Value] ), SUMX ( t_BC, [Value] ) )

Is this what you want to achieve?

ERD_0-1701278687587.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Answers my question perfectly. Thank you for taking the time to help me.
I was wondering if splitting my table was going to be neccessary as I wasn't aware of the virtual table function. I really appreciate the pointers!

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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