Forum Discussion
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:
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:
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:
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!
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?
4 Replies
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?
