Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Divide Function Measure in Matrix Percentage Overall Total Incorrect

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. ...
General Comment
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
amitchandak
Super User
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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?