Forum Discussion
Create a calculated column with condition
Good afternoon
I want to create a calculated column as a "Flag" with just 0's and 1's if the units where less than projected. When doing so, I get the addition of all rows where the condition was met, not just using the overall totals, how can I do that?
This is my database:
| STATE | ID | TYPE | UNITS | PROJECTED |
| FLORIDA | 1234 | CAR | 1000 | 1050 |
| FLORIDA | 1234 | VAN | 900 | 910 |
| FLORIDA | 1234 | SUV | 800 | 840 |
| FLORIDA | 4573 | BIKE | 760 | 800 |
| FLORIDA | 4573 | CAR | 890 | 900 |
| FLORIDA | 4578 | VAN | 755 | 700 |
This is my formula for the "flag" column:
FLAG = IF (UNITS < PROJECTED, 1, 0)
And this is what I am getting:
| STATE | ID | UNITS | PROJECTED | CURRENT FLAG | EXPECTED FLAG |
| FLORIDA | 1234 | 2700 | 2800 | 3 | 1 |
| FLORIDA | 4573 | 1650 | 1700 | 2 | 1 |
| FLORIDA | 4578 | 755 | 700 | 0 | 0 |
I understand why the current flag returns those numbers (it is adding the total times the condition was met by "type" and "ID", but I need that flag to be the EXPECTED FLAG column, with just 0's and 1's.
Please note that the TYPE column will be a slicer in the dashboard, not included in the table, so I need the final table to show if the overall ID units (depending on option(s) selected) where less than projections.
Thank you for your support.
Anonymous try this as a calculated column
flag = VAR _units = CALCULATE ( SUM ( t2[UNITS] ), ALLEXCEPT ( t2, t2[ID] ) ) VAR _proj = CALCULATE ( SUM ( t2[PROJECTED] ), ALLEXCEPT ( t2, t2[ID] ) ) RETURN IF ( _units < _proj, 1, 0 )
14 Replies
Anonymous try this as a calculated column
flag = VAR _units = CALCULATE ( SUM ( t2[UNITS] ), ALLEXCEPT ( t2, t2[ID] ) ) VAR _proj = CALCULATE ( SUM ( t2[PROJECTED] ), ALLEXCEPT ( t2, t2[ID] ) ) RETURN IF ( _units < _proj, 1, 0 )This works perfect as a solution, but when I build a measure I get the wrong result. I want to create a measure that sums (or distinctcounts whichever works) all the 1's obtained in that column.
Again, I need to sum just the ID's that had a 1, but I also need the measure to NOT CONSIDER (exclude) or SELECTALL STATES regardless of the slicer built in the dashboard.
The idea is to build a measure with the OVERALL result (this case) ignoring what state is selected, just sum/count all 1's at a "system level", and a measure that responds to the state filter (which I already built).
Is this possible?
If you want a measure that ignores filters you can try this. Lets say you calculate the sum (easy example)
= SUM(Sales[Flag])
But you want the calculation to work no matter what you filter you can use:
= CALCULATE(SUM(Sales[Flag]), ALL(Sales[STATE]))
Your formula seems fine, it seems like it is summarizing the result. Did you check if its set to "Dont Summarize" ?
See my attached screenshots. (In my screenshot its set to Sum, make sure it isnt)
