Forum Discussion
Measure returning same value for all rows in matrix
Hi experts,
I need help with figuring out why the measure repeats the same value (Which is correct in its total)
I want to calculate the sum of backlog of current month (and compare to previous month depending on user selection) and display it by entity LE. There are multiple currencies in my table, but in a page filter only "EUR" are selected.
Δ PM Backlog =
VAR Date_CurrentMonth =
SELECTEDVALUE ( 'POC Data'[MonthEndDate] )
VAR Date_PreviousMonth =
EOMONTH ( EDATE ( SELECTEDVALUE ( 'POC Data'[MonthEndDate] ), -1 ), 0 )
VAR Backlog_CurrentMonth =
CALCULATE(
SUM ( 'POC Data'[IFRS Backlog] ),
'POC Data'[MonthEndDate] = Date_CurrentMonth
)
VAR Backlog_PreviousMonth =
CALCULATE(
SUM ( 'POC Data'[IFRS Backlog] ), FILTER(ALLEXCEPT('POC Data', 'POC Data'[Currency]),
'POC Data'[MonthEndDate] = Date_PreviousMonth)
)
RETURN Backlog_PreviousMonth /1000
/* IF (
NOT ISBLANK ( Backlog_PreviousMonth ),
( Backlog_CurrentMonth - Backlog_PreviousMonth ) / 1000
)*/MonthEndDate is on the n:1 side of a relationship with a date table.
Clearly I am doing something wrong in my filtering for BAcklog_PreviousMonth.
Any help appreciated, as always.
Your other column names are not shown in your pic, but try the expression below for your variable. I assume you are using the Date_EndMonth column in your slicer, so you need to remove that filter (but not more than that). If so, that is why ALLSELECTED didn't work; it would not remove the filter coming from that slicer. Try just removing only the filter from that column (and if you have a sort column for it, remove that one too).
VAR Backlog_PreviousMonth = CALCULATE( SUM ( 'POC Data'[IFRS Backlog] ), ALL('POC Data'[MonthEndDate], 'POC Data'[MonthSortColumn]), 'POC Data'[MonthEndDate] = Date_PreviousMonth)Regards,
Pat
4 Replies
Your ALLEXCEPT is removing filters from all the columns in that table except for the currency column. Try using ALL or ALLSELECTED to remove filters from just the columns you need.
Regards,
Pat
Hi Pat,
I had already tried all kind of combinations, but here are results:
VAR Backlog_PreviousMonth = CALCULATE( SUM ( 'POC Data'[IFRS Backlog] ), FILTER(ALLSELECTED('POC Data'), 'POC Data'[MonthEndDate] = Date_PreviousMonth) )returns BLANK
VAR Backlog_PreviousMonth = CALCULATE( SUM ( 'POC Data'[IFRS Backlog] ), ALL('POC Data'), 'POC Data'[MonthEndDate] = Date_PreviousMonth, 'POC Data'[Currency]="EUR" )returns same value for all rows
Your other column names are not shown in your pic, but try the expression below for your variable. I assume you are using the Date_EndMonth column in your slicer, so you need to remove that filter (but not more than that). If so, that is why ALLSELECTED didn't work; it would not remove the filter coming from that slicer. Try just removing only the filter from that column (and if you have a sort column for it, remove that one too).
VAR Backlog_PreviousMonth = CALCULATE( SUM ( 'POC Data'[IFRS Backlog] ), ALL('POC Data'[MonthEndDate], 'POC Data'[MonthSortColumn]), 'POC Data'[MonthEndDate] = Date_PreviousMonth)Regards,
Pat
Hi mahoneypat ,
excellent, thank you! I removed the filters form my date table, I didn't realize it's passed on.
Attached are more screenshots to explain your solution for my scenario, maybe somebody else will benefit.....this worked:
VAR Backlog_PreviousMonth = CALCULATE( SUM ( 'POC Data'[IFRS Backlog] ), ALL(DateTab), 'POC Data'[MonthEndDate] = Date_PreviousMonth )my relationship:
