Create a calculated column with condition

[Anonymous]

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.

[Tweeg]

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)

[smpa01 · Accepted solution]

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

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?

[smpa01]

@Anonymous  do you mean this?

If not, paste a screenshot of your desired result

[Anonymous]

Good morning, 

Sorry about my last email, I made a mistake.

You are getting the right result, however, the "measure 2" result output (total sum if you would, that you would see in a card visualization) should be two (2) because there were 2 ID's out of the total 3 that had units < projected.

You are geeting a total of 5 because it is summarizing all the ONE's I just need a measure that "distinctcounts" or similar the drive ID's that had a one.

Also, I need that distinct count NOT TO CONSIDER a state filter that will be in the dashboard, as reference, there is a helper table called "State" that has the unique states.

so for example, it would be something like:

= caluclate( Distinctcount( ID), ALL(STATE TABLE), MEASURE2=1) but this formula doesn't give me the result I need.

You could use my last post to see the updated "test" file you sent me.

Thank you for your support. 

[smpa01]

@Anonymous  I am loosing you here. Can you please explicitly show the result you want (with numbers)? 

[Anonymous]

I am sorry.

I want a 5, see the picture, and I also want that measure, (or the new one) to be unresponsive to a STATE slicer that will be in the dashboard (this slicer comes from another table):

[smpa01]

@Anonymous  and for exact what reason you excluded 7789 from that? Should it not be 6 and not 5?

[Anonymous]

Yes you are correct, I didn't scroll down enough, it should be 6. It was my mistake.

[smpa01]

@ the best I can come up with this

Measure 3 = CALCULATE(DISTINCTCOUNT(t2[ID]),FILTER(t2,t2[flag]=1))
forCard = CALCULATE(CALCULATE(DISTINCTCOUNT(t2[ID]),FILTER(t2,t2[flag]=1)),ALL(t2))

[Anonymous]

I thought about that, but the problem is that the card is not responsive to other filters in the dashboard, I have vehicle type, fiscal year, fiscal month, and others.

[Anonymous]

Hi,

Thak you for your answer, but no, that's not what I mean, I have edited your test file so you can understand what's happening to me.

If you change the selections, the measure number changes, I need it to always show the total number of 1's (select all options in slicer), regardless of the state I select, that's how I get to show the "system" overall result.

See following link (forum doesn't allow me to attach a PBI file)

https://1drv.ms/u/s!Av0WzGMqPtTonimUcDlilsZ2DXQz

Thank you again so much for your support.

[Tweeg]

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]

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.