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

 

 
 
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. 

3 Replies

  • 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?  

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.