IF Statement Total Not Summing Correctly Correctly In Measure

[alee5210]

Hi All, I'm trying to create an IF statement that has the condition, followed by a 1 or 0 that will sum.

My data looks like the following BUT I only have GroupField1/2 and SaleA/B/C. The other columns added are measures and are there to make things easier to understand.

Group Field 1		Group Field 2		SaleA		SaleB		SaleC		Dist.CountA		Dist.CountB		Dist.CountC		Overall
x		a		1		5		9		1		2		1		0
x		a		1		6		9		1		2		1		0
y		b		2		4		8		1		1		1		1
y		c		3		4		8		1		1		1		1
z		d		4		1		7		1		1		1		1
z		d		4		1		7		1		1		1		1
z		d		4		1		7		1		1		1		1

There are a number of grouping columns but in this example I've just used 2 as an example.

What I'm trying to do is for all grouping fields, how many distinct values are there in SaleA/B/C.

So the first 2 lines, GroupField1/2 are both the same, and there is the same value in SaleA so Dist.CountA has the value 1.

For the first 2 lines, GroupField1/2 are both the same but SaleB has 2 different values, so Dist.CountB has the value of 2.

I have already created the measures for DistCountA/B/C using the following formula

Dist.CountA = calculate(distinctcount(data[SaleA]), Groupby(data, data[GroupField1], data[GroupField2],...data[GroupFieldN]))

That is how I get the values in the table above and it works well.

For the overall column, I want the logic to be like this.

Overall = if([DistCountA] = 1 && [DistCountB] = 1 && [DistCountC] = 1, 1, 0)

When I put all these measures in a table (Dist.CountA/B/C and Overall), it does seems like it works at the row level, but when I put 'Overall' into a card or see the Total for Overall at the bottom of the table, it is only showing 0. What am I doing wrong?

[rajendraongole1 · Accepted solution]

Hi @alee5210 - Instead of using the row-level logic directly in the measure, you can aggregate the logic using SUMX. This will apply the IF condition for each group and then sum the results.

Overall =
SUMX (
SUMMARIZE (
data,
data[GroupField1],
data[GroupField2],
"DistCountA", CALCULATE ( DISTINCTCOUNT ( data[SaleA] ) ),
"DistCountB", CALCULATE ( DISTINCTCOUNT ( data[SaleB] ) ),
"DistCountC", CALCULATE ( DISTINCTCOUNT ( data[SaleC] ) )
),
IF ( [DistCountA] = 1 && [DistCountB] = 1 && [DistCountC] = 1, 1, 0 )
)

try the above logic and i hope it works

[alee5210]

This is exactly what I was after. I still need to do some additional testing, but will this recalculate based on any slicers applied? E.g. If I filter data to a single store, or if there was an additional date field that I then filtered to be between a specific range, would this still work and be effective?