Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
PshemekFLK
Helper IV
Helper IV

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_granularityCALCULATE(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):

 

PshemekFLK_0-1651585568414.png

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!

 

 

 

 

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

Simplify it.  Get rid of the SUMMARIZE code

View solution in original post

4 REPLIES 4
HotChilli
Super User
Super User

Simplify it.  Get rid of the SUMMARIZE code

@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])
HotChilli
Super User
Super User

Try changing

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

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.