cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aminw0u Frequent Visitor
Frequent Visitor

Summary Error with IF Statement

Hello,

 

I'm having issues with an IF and/or SUMX formula. I essentially need the calculation done at every line level & then aggregated. With my first attempt using IF, the results are correct at the line level but don't sum up properly. It should show $21K, not $35K.

 

I assume I really need to be using SUMX, but that doesn't seem to be calcing at the line level correctly. Can anyone help pinpoint what I'm doing wrong? Here are my measures:

 

 

08.a Expired Release via Scrap = IF([01. Expired PQ] < [07. Gross Scrap] , [01. Expired PQ] , [07. Gross Scrap] ) * -1

 

08.a2 Expired Release via Scrap = CALCULATE(SUMX(EEORE, IF([01. Expired PQ] < [07. Gross Scrap] , [01. Expired PQ] , [07. Gross Scrap]) * -1 ))

 

 

Example.PNG

 

 

Thanks! 

J

 

11 REPLIES 11
Phil_Seamark Super Contributor
Super Contributor

Re: Summary Error with IF Statement

Hi @aminw0u

 

Are the lines in your matrix an exact match to the lines in your source data?  Or are these aggregated values?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

aminw0u Frequent Visitor
Frequent Visitor

Re: Summary Error with IF Statement

They are aggregated on some level. I have a data set with 34 columns & I only pulled 7 of those into my filter to drill down through before I got to what you see here.

Phil_Seamark Super Contributor
Super Contributor

Re: Summary Error with IF Statement

So for SKU Name 87 you have a values of $71 and $88

 

Are these values based on multiple rows that have been aggretated to this single line?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Phil_Seamark Super Contributor
Super Contributor

Re: Summary Error with IF Statement

Here is a cut down model using your exact formula showing it working ok

 

https://1drv.ms/u/s!AtDlC2rep7a-oB-2n_WxkVzte_RD

 

So just trying to work out the differences.  Are you using calculated measures or calculated columns?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

aminw0u Frequent Visitor
Frequent Visitor

Re: Summary Error with IF Statement

Yes, there are several lines which make up that particular row. If it helps, my data is inventory data so it goes down to the SKU, manufacturing site & current branch.

 

BranchCode.PNG

aminw0u Frequent Visitor
Frequent Visitor

Re: Summary Error with IF Statement

Thanks. I'm using measures. The Expired PQ & Gross Scrap are measures as well. I had tried calculated columns earlier & got  circular references, so that didn't seem like the way to go.

 

Is it possible that this is just a bug? I would have expected my formula to work the same as what you have in your file, and I've had several instances of formulas not working when they should have (including while at a DIAD training with Microsoft instructors looking over my shoulder wondering why it wasn't working).

Phil_Seamark Super Contributor
Super Contributor

Re: Summary Error with IF Statement

Possibly but this seems like it should be pretty straight forward.  What version of Power BI Desktop are you using?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Phil_Seamark Super Contributor
Super Contributor

Re: Summary Error with IF Statement

Are the detail lines from the same EEORE table or from a related table?  I think we are getting close to the issue.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

aminw0u Frequent Visitor
Frequent Visitor

Re: Summary Error with IF Statement

Everything is pulling from the same table (which is the original data load).

 

I'm using the latest version that was released Nov 7th.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 75 members 1,117 guests
Please welcome our newest community members: