Forum Discussion
IF Statement Total Not Summing Correctly Correctly In Measure
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?
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
2 Replies
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
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?
