Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Total is wrong for DIVIDE function

Hello. I am not able to get the total for this formula to accurate total. The column I’m referring to is LostRevYTDSUM. It should be $10,934 and not $7,718. I have shown the detail to the left. I wont be using the ParentPartnerId in my final calculation. It will only be the year and month nbr like on the right. I just included it for detail. I have tried everything. Any suggestions would be appreciated.

LostRevYTDSum =
VAR YTDFiscalMonthNbr = IF(CALCULATE( MAX('Calendar'[fiscal_month_nbr]), 'Calendar'[calendar_date] = TODAY()) = 1, 12,
(CALCULATE( MAX('Calendar'[fiscal_month_nbr]), 'Calendar'[calendar_date] = TODAY()) -1) )

VAR SalesLast24M = CALCULATE(
SUM(ParentPartnerMonthlySales[NetSales]),
CALCULATETABLE (
DATESINPERIOD ('Calendar'[calendar_date], MAX ( PartnerDates[ParentLostCustomerDate] ), -24, MONTH ),
USERELATIONSHIP ( PartnerDates[fiscal_month_end_date] , FiscalMonthEndDate[fiscal_month_end_date] ) ) )

VAR NumOfNonZeroMonths = CALCULATE(
COUNT(ParentPartnerMonthlySales[FiscalMonthEndDate]),
CALCULATETABLE(
DATESINPERIOD('Calendar'[calendar_date], MAX( PartnerDates[ParentLostCustomerDate] ),-24,MONTH),
USERELATIONSHIP( PartnerDates[fiscal_month_end_date], FiscalMonthEndDate[fiscal_month_end_date] ),
NOT(isblank(ParentPartnerMonthlySales[NetSales]))))
 
RETURN
SUMX(
VALUES('Calendar'[fiscal_month_nbr]),
DIVIDE( SalesLast24M, NumOfNonZeroMonths ) * YTDFiscalMonthNbr /-1
)
Need Help
  • DataInsights's avatar
    DataInsights
    4 years ago

    Try this:

     

    LostRevYTDSum =
SUMX (
    VALUES ( ParentPartnerMonthlySales[ParentPartnerID] ),
    [LostRevYTDSumCalc]
)
  • Anonymous's avatar
    Anonymous
    4 years ago

    I figured it out. Thank you for the suggestion. I just started trying all the columns. 🙂 Appreciate it

     

8 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    4 years ago

    Anonymous,

     

    Try splitting the measure into two measures: one measure to do the calculation, and a second measure to calculate totals. The base measure is named "Calc", and the totals measure has the original name. In the totals measure (SUMX), you might need to iterate a column with both fiscal_year_nbr and fiscal_month_nbr (e.g., 202202), instead of just fiscal_month_nbr.

     

    LostRevYTDSum Calc =
VAR YTDFiscalMonthNbr =
    IF (
        CALCULATE (
            MAX ( 'Calendar'[fiscal_month_nbr] ),
            'Calendar'[calendar_date] = TODAY ()
        ) = 1,
        12,
        (
            CALCULATE (
                MAX ( 'Calendar'[fiscal_month_nbr] ),
                'Calendar'[calendar_date] = TODAY ()
            ) - 1
        )
    )
VAR SalesLast24M =
    CALCULATE (
        SUM ( ParentPartnerMonthlySales[NetSales] ),
        CALCULATETABLE (
            DATESINPERIOD (
                'Calendar'[calendar_date],
                MAX ( PartnerDates[ParentLostCustomerDate] ),
                -24,
                MONTH
            ),
            USERELATIONSHIP ( PartnerDates[fiscal_month_end_date], FiscalMonthEndDate[fiscal_month_end_date] )
        )
    )
VAR NumOfNonZeroMonths =
    CALCULATE (
        COUNT ( ParentPartnerMonthlySales[FiscalMonthEndDate] ),
        CALCULATETABLE (
            DATESINPERIOD (
                'Calendar'[calendar_date],
                MAX ( PartnerDates[ParentLostCustomerDate] ),
                -24,
                MONTH
            ),
            USERELATIONSHIP ( PartnerDates[fiscal_month_end_date], FiscalMonthEndDate[fiscal_month_end_date] ),
            NOT ( ISBLANK ( ParentPartnerMonthlySales[NetSales] ) )
        )
    )
RETURN
    DIVIDE ( SalesLast24M, NumOfNonZeroMonths ) * YTDFiscalMonthNbr / -1

     

    LostRevYTDSum =
SUMX ( VALUES ( 'Calendar'[fiscal_month_nbr] ), [LostRevYTDSum Calc] )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I tried your suggestion but I still cant get the right total. One issue is that the dates are fiscal and the Fiscal_year_month_name is a text. we dont have a year and month in the numeric. VALUES only takes one column. I even tried just using the calendar table name only. Same result. Any other thoughts?

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I tried using UNION. The total is closer but it shoudl be $10,934.

       

      • DataInsights's avatar
        DataInsights
        Super User
        4 years ago

        Try this:

         

        LostRevYTDSum =
SUMX (
    VALUES ( ParentPartnerMonthlySales[ParentPartnerID] ),
    [LostRevYTDSumCalc]
)