Total is wrong for DIVIDE function

[Anonymous]

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

)

[DataInsights · Accepted solution]

Try this:

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

[DataInsights]

@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]

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

[DataInsights · Accepted solution]

Try this:

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

[Anonymous]

That gets me nothing.

[DataInsights]

I would need to see your data model. Be sure to remove sensitive data before uploading the pbix. You can use one of the file services such as OneDrive and post the link here.

[Anonymous · Accepted solution]

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

[Anonymous]

unfortunately its connected to a server. Not sure how Id recreate it.

[Anonymous]

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?