Forum Discussion

MelStaunton's avatar
MelStaunton
Helper III
5 years ago
Solved

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

    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

mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

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

MelStaunton's avatar
MelStaunton
Helper III
5 years ago

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: