Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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...
Need Help
  • smpa01's avatar
    smpa01
    5 years ago

    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 )

     

     

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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?

Tweeg's avatar
Tweeg
Icon for Helper I rankHelper I
5 years ago

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]))

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Right, but if I do that, it doesn't consider just the 1's that I assigned to each ID, it will use 1's on each row that the condition is satisfied.

     

    I tried:

     

    Measure = CALCULATE(SUM('Sales'[Flag]), ALL('Sales'[STATE]),FILTER('Sales','Sales'[FLAG]=1))

     

    But I get a greater value than the one I should get, although now the state slicer doesn't affect my measure.