Forum Discussion
Create a calculated column with condition
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 )
Community ChampionAnonymous 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?
TweegHelper I
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]))
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.
- smpa01
Anonymous do you mean this?
If not, paste a screenshot of your desired result
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.
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)?