Incorrect Total

PshemekFLK — Tue, 03 May 2022 14:10:41 GMT

I'm running into a problem with incorrect totals in my measure despite using a SUMMARIZE formula which based on articles online should fix the issue.

"Forecast Sum" = Forecast on the product family level

"Forecast Split by Item" = with this measure I'm trying to allocate [Forecast Sum] to item level based on bookings in the prior year

Forecast Sum = Sum(Forecast[Net USD])

Forecast Split by Item = Forecast[Forecast Sum]*[Item Allocation]

Item Allocation =

VAR Bookings_PY_FY = CALCULATE(Bookings[BookingsPY],all(dim_calendar),values(dim_calendar[Year]))

VAR Bookings_PY_FY_at_pfam_granularity = CALCULATE(Bookings[BookingsPY],all(dim_calendar),values(dim_calendar[Year]),ALL(dim_product),VALUES(dim_product[Product Family]))

RETURN

DIVIDE(Bookings_PY_FY,Bookings_PY_FY_at_pfam_granularity)

With this everything is calculated correctly except that if I add "Workflow" (another level of product hierarchy) to the matrix the total per workflow is not correct (6,810+5,723=12,533 not 14,574):

I tried to fix this with Summarize formula but doesn't change the result:

Forecast Split by Item_Summarize =
SUMX (
    SUMMARIZE (
        VALUES ( dim_product[Item No] ),
        "Forecast", Forecast[Forecast Sum],
        "Item Alloaction", Bookings[Item Allocation]
    ),
    [Forecast] * [Item Alloaction]
)

pbix file: https://fastupload.io/OZS4Z87oPbjVRKa

What am I missing?

Thanks!

Re: Incorrect Total

HotChilli — Tue, 03 May 2022 15:18:34 GMT

Try changing

Forecast Split by Item to be a SUMX over the Product Family (you'll need a VALUES function in there)

Re: Incorrect Total

PshemekFLK — Wed, 04 May 2022 11:57:15 GMT

HotChilli, that's what I tried to do with "Forecast Split by Item_Summarize" measure but it didn't fix the issue

Re: Incorrect Total

HotChilli — Wed, 04 May 2022 12:14:14 GMT

Simplify it. Get rid of the SUMMARIZE code

Re: Incorrect Total

PshemekFLK — Thu, 05 May 2022 10:09:59 GMT

HotChilli it worked! many thanks for help!

Correct formula:

Forecast Split by Item = SUMX(VALUES(dim_product[Item No]),Forecast[Forecast Sum]*Bookings[Item Allocation])

topic Incorrect Total in DAX Commands and Tips

Incorrect Total

Re: Incorrect Total

Re: Incorrect Total

Re: Incorrect Total

Re: Incorrect Total