Forum Discussion

PshemekFLK's avatar
PshemekFLK
Helper IV
4 years ago
Solved

Incorrect Total

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!

 

 

 

 

Need Help
Need Help DAX measure

4 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    Try changing

    Forecast Split by Item to be a SUMX over the Product Family (you'll need a VALUES function in there)
    • PshemekFLK's avatar
      PshemekFLK
      Helper IV
      4 years ago

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

  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    Simplify it.  Get rid of the SUMMARIZE code

    • PshemekFLK's avatar
      PshemekFLK
      Helper IV
      4 years ago

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