cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

SUMMARIZE Not Returning Correct Sum Value

Hello,

 

I'm trying to perform a SUM of the Production Qty at the Scheduled Shift level which is the next level of granularity from MATERIAL_NUMBER but I'm not getting the correct summation. I performing this summation so I can calculate a weighted average for a downstream calculation.

 

The screenshot below shows what should be happening. For Shift 1, the field "Production Qty (Date-PlantNum-...) is the measure that I'm using the SUMMARIZE function in to sum the Production Quantity at the Shift level. Instead of returning 1,541, the values being returned are at the MATERIAL_NUMBER level....653 and 888. The Weighted Average should then be .4237 and .576 instead of 1.0000.

 

SummarizeNotReturningCorrectValue.PNG

 

 

Any guidance would be appreciated here.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver I
Resolver I

Re: SUMMARIZE Not Returning Correct Sum Value

Using the "Group By" functionality in Power Query Editor worked for me. I followed the instructions near the bottom of this article link...Link 

View solution in original post

7 REPLIES 7
Highlighted
Regular Visitor

Re: SUMMARIZE Not Returning Correct Sum Value

Hi. I'm going to try and take a stab at it. What if you had the measure as something like...

Production Qty = CALCULATE(SUM('Table'[Production Quantity]), ALLEXCEPT('Table', 'Table'[Scheduled Shift]))

... where the values for 'Table' are whatever table you're sourcing from.

The first 'Table' is the table that [Production Quantity] comes from, second 'Table' is that same table. The third 'Table' is the table from which [Scheduled Shift] is sourced from.

As for the weighted average (this is more of a proportion or weighted average coefficient, but a nomenclature debate is out of scope), you just do,

Weight Av = DIVIDE([Production Quantity],[Production Qty])

...but based on your values of 1.000, it looks like you may already be doing something similar.

 

Hope this helps.

Highlighted
Resolver I
Resolver I

Re: SUMMARIZE Not Returning Correct Sum Value

@mmadigan 

 

That's what I had originally but the records inflate as the ALLEXCEPT() function ignores any relationships configured in the data model and creates a foreach combination result.

Highlighted
Regular Visitor

Re: SUMMARIZE Not Returning Correct Sum Value

That makes sense.

Can you post the formula you entered using SUMMARIZE to derive the 'Production Qty' measure?

Highlighted
Resolver I
Resolver I

Re: SUMMARIZE Not Returning Correct Sum Value

Here's the DAX behind the measure...

 

Production Qty (Date-PlantNum-PlantType-ProdLine-Shift) =
SUMX(
SUMMARIZE('HANA Production Fact','HANA Production Fact'[Scheduled Date], 'HANA Production Fact'[Plant], 'HANA Production Fact'[COS Production Line], 'HANA Production Fact'[Scheduled Shift],"Production Qty 1",CALCULATE(SUM('HANA Production Fact'[Production Quantity]))),[Production Qty 1])
Highlighted
Regular Visitor

Re: SUMMARIZE Not Returning Correct Sum Value

Maybe this will work...

 

CALCULATE(
SUM('HANA Production Fact'[Production Quantity]),
ALLEXCEPT(HANA Production Fact,
'HANA Production Fact'[Scheduled Date],
'HANA Production Fact'[Plant],
'HANA Production Fact'[COS Production Line],
'HANA Production Fact'[Scheduled Shift]
)
)

Highlighted
Resolver I
Resolver I

Re: SUMMARIZE Not Returning Correct Sum Value

You DAX proposal includes ALLEXCEPT() which will inflate my records. Unfortunately, this method won't work. I'm going to try the "Group By" functionality within the Power Query Editor and see if that gives me the desired results. I appreciate your efforts in trying to find a solution here!

Highlighted
Resolver I
Resolver I

Re: SUMMARIZE Not Returning Correct Sum Value

Using the "Group By" functionality in Power Query Editor worked for me. I followed the instructions near the bottom of this article link...Link 

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors