Forum Discussion
Measure Total incorrect
Hi,
I am struggling with a measure total. My row total is fine but the grand total at the bottom is wrong (just the one cell!) I can see why it is wrong but I'm not sure how to fix it.
If you look at the image you will see that I have planned and an actual colum.
planned is measure summing:-
= CALCULATE (
SUM('Activity_view (2)'[Hours]),
FILTER (
'Activity_view (2)',
'Activity_view (2)'[SQL_ID] = 2
)
)
unplanned is a measure summing this:-
= CALCULATE (
SUM('Activity_view (2)'[Adherance Hours]),
FILTER (
'Activity_view (2)',
'Activity_view (2)'[SQL_ID] = 1 && 'Activity_view (2)'[transtype] = 2
)
)
and additonal to planned this:-
= IF ([Over Hours Actual Planned] - [Over Hours Planned] <0 , 0 , SUMX('Activity_view (2)',[Over Hours Actual Planned] - [Over Hours Planned]))
I have tried this and it actually returns nothing:-
= IF(HASONEFILTER('Activity_view (2)'[UserID]),
IF ([Over Hours Actual Planned] - [Over Hours Planned] <0 , 0 , SUMX('Activity_view (2)',[Over Hours Actual Planned] - [Over Hours Planned])))
Any help would be appreciated.
ok, this has been causing me a problem all day but ive cracked it.
I created a second measure that uses the first:-
Over_Hours_Unplanned_Step_1 = IF(SUMX('Activity_view (2)',[Over Hours Actual Planned] - [Over Hours Planned]) < 0, 0, [Over Hours Actual Planned] - [Over Hours Planned])
Over_Hours_Unplanned_Step_2 = VAR __Unplanned = Summarize('Activity_view (2)','Activity_view (2)'[UserID],"__value",[Over Hours Unplanned_S1])
RETURN
IF(HASONEVALUE('Activity_view (2)'[UserID]),[Over Hours Unplanned_S1],SUMX(__Unplanned,[__value]))and all is well!
3 Replies
Hi, Anonymous,
you don't get 0 in the column total of [additional to planned] because [actual] is greater than [planned], but because in your if-statement use hasonefilter([UserID]). In the total you don't have a filter on UserID, as you want to see the sum for all users which satisfy the conditions.
Try this instead
Actual - Planned = VAR _tmp = SUMX ( FILTER ( 'Table'; 'Table'[Actual] > [Planned] ); 'Table'[Actual] - 'Table'[Planned] ) RETURN IF ( ISBLANK ( _tmp ); 0; _tmp )The code between VAR and RETURN is not neccessary, it is just there to make to code cleaner and easier to read, as you only need to write the SUMX-part once.
Hi,
I am struggling somewhat with this, i have tried your suggestion but my values are measures they aggregated rows together first.
I tried your with the measures and with the full syntax as if the measure didn’t exist but i can’t get it working.
the measure for actual and planned is below...
Over Hours Actually Worked = CALCULATE (
SUM('Activity_view (2)'[Adherence Hours]),
FILTER (
'Activity_view (2)',
'Activity_view (2)'[SQL_ID] = 1 && 'Activity_view (2)'[transtype] = 2
)
)Over Hours Planned = CALCULATE (
SUM('Activity_view (2)'[Hours]),
FILTER (
'Activity_view (2)',
'Activity_view (2)'[SQL_ID] = 2
)
)I need a way of taking one from the other (if actual is greater than 0 then show a value else 0) and this works using the below measure it’s just all the totals are 0? it’s got me stuck!
This is another way I have tried, and it again works for the row but not the total...
Over Hours Unplanned = IF ([Over Hours Actually worked] - [Over Hours Planned] <0 , 0 , [Over Hours Actually worked] - [Over Hours Planned])
Your help would be greatly appreciated.
ok, this has been causing me a problem all day but ive cracked it.
I created a second measure that uses the first:-
Over_Hours_Unplanned_Step_1 = IF(SUMX('Activity_view (2)',[Over Hours Actual Planned] - [Over Hours Planned]) < 0, 0, [Over Hours Actual Planned] - [Over Hours Planned])
Over_Hours_Unplanned_Step_2 = VAR __Unplanned = Summarize('Activity_view (2)','Activity_view (2)'[UserID],"__value",[Over Hours Unplanned_S1])
RETURN
IF(HASONEVALUE('Activity_view (2)'[UserID]),[Over Hours Unplanned_S1],SUMX(__Unplanned,[__value]))and all is well!
