Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MelStaunton
Helper III
Helper III

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.
Screenshot Matrix.png

Δ 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.

Mel
@dedelman_clng 

1 ACCEPTED SOLUTION

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
MelStaunton
Helper III
Helper III

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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:

Screenshot Relationship.png

mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.