Divide Function Measure in Matrix Percentage Overall Total Incorrect

[Anonymous]

I have reviewed numerous topics in this forum, but haven't found one that fits my issue exactly.  I have two measures that I've created to use with the DIVIDE function to obtain the overall % total. MeasureA and MeasureB work as intended in the Matrix. But the outcome of the DIVIDE measure, MeasureC, is incorrect. The overall % total should be 112.35%, not 26.21%.

The table has a 1:1 relationship with the calendar table by date. 

MeasureA

Revenue 2023 Actuals Total Excluding 2022 =

IF(

    ISINSCOPE('Calendar'[QuarterInCalendar]),

    [Revenue 2022_2023 Actuals Both],

    CALCULATE([Revenue 2022_2023 Actuals Both],

    FILTER(ALLSELECTED('Calendar'),

    'Calendar'[QuarterInCalendar] <> "Q1 2022"

    && 'Calendar'[QuarterInCalendar] <> "Q2 2022"

    && 'Calendar'[QuarterInCalendar] <> "Q3 2022"

    && 'Calendar'[QuarterInCalendar] <> "Q4 2022"

    )

)

)

MeasureB

Revenue 2023 Month Plan Total Excluding 2022 =

IF(

    ISINSCOPE('Calendar'[QuarterInCalendar]),

    [Revenue 2022_2023 Month Plan Both],

    CALCULATE([Revenue 2022_2023 Month Plan Both],

    FILTER(ALLSELECTED('Calendar'),

    'Calendar'[QuarterInCalendar] <> "Q1 2022"

    && 'Calendar'[QuarterInCalendar] <> "Q2 2022"

    && 'Calendar'[QuarterInCalendar] <> "Q3 2022"

    && 'Calendar'[QuarterInCalendar] <> "Q4 2022"

    )

)

)

MeasureC (I need to help to correct the total)

DIVIDE(

  [Revenue 2023 Actuals Total Excluding 2022],

  IF([Revenue 2023 Month Plan Total Excluding 2022]=0, BLANK(), [Revenue 2023 Month Plan Total Excluding 2022])

)

 Current Matrix:

[Anonymous · Accepted solution]

I have figured out what I was doing wrong. The problem was MeasureB. I have fixed MeasureB and it looks like this: 

CALCULATE(

           SUM('Revenue_Actuals Plan'[Plan(BG)]),

          FILTER('Calendar',

          'Calendar'[Date] <= TODAY() &&

          YEAR('Calendar'[Date]) <> 2022

          )

)

Now MeasureC is working perfectly!!!

 I hope this helps someone else in the future. 

Please close this question. 

[amitchandak]

@Anonymous , Try like

Revenue 2023 Actuals Total Excluding 2022 =
IF(
ISINSCOPE('Calendar'[QuarterInCalendar]),
[Revenue 2022_2023 Actuals Both],
CALCULATE([Revenue 2022_2023 Actuals Both],
FILTER(('Calendar'),
year('Calendar'[Date]) <> 2022
))
)

MeasureB
Revenue 2023 Month Plan Total Excluding 2022 =
IF(
ISINSCOPE('Calendar'[QuarterInCalendar]),
[Revenue 2022_2023 Month Plan Both],
CALCULATE([Revenue 2022_2023 Month Plan Both],
FILTER(('Calendar'),
year('Calendar'[Date]) <> 2022
))
)

MeasureC (I need to help to correct the total) =
DIVIDE(
[Revenue 2023 Actuals Total Excluding 2022],
[Revenue 2023 Month Plan Total Excluding 2022]
)

[Anonymous]

Thank you for your quick response.

I tried your suggestion, but it gives me the same result of 26.21% instead of 112.35%. I see now why I'm getting 26.21%.  MeasureB is summing future months' values and MeasureA has a value of $0 for future months.   How do I exclude future month values from MeasureB?