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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Kfausch
Helper II
Helper II

How to SUM a measure using MAX

I have a table in which I am trying to calculate product waste, the issue is I need to SUM the yield but only on the last entry for a particular document.

 

This is the forumla I am currently using which gives me exactly what I need in the rows, but in the table total it gives me the MAX instead of the tables SUM.

Actual_Yield = CALCULATE(SUM('Capacity Personnel'[No_Employees]), 'Capacity Personnel'[No] = "PCA",
FILTER('Capacity Personnel',
[Entry_No]=MAX([Entry_No])))
 
Expected_Yield is the same but using "PCE" instead of "PCA"
Units Short = [Expected_Yield]-[Actual_Yield]
Waste% = DIVIDE([Units Short],[Expected_Yield])
 
Any guidance is greatly appreciated! Thanks for reading.
PowerBI.PNG
1 ACCEPTED SOLUTION

I see what you mean - have you considered creating a deduped version of the table then calculating for each row, eliminating the dedupe inside the calc? 

View solution in original post

7 REPLIES 7
ThomVF
Advocate IV
Advocate IV

Looks like it is giving you the last value calculated, not the MAX?

Are you using calculated columns or calculated measures?

 

 

 

I am currently trying with meausures, but I tried to make it work with calculated columns as well.

I found this tutorial/article - maybe it sheds some light on whats happening...

 

https://www.jetglobal.com/blog/calculated-columns-vs-measures/

It's actually doing both each line in the screen shot represents a Production Order where we produced an item. When we do this we log what the Expected Yield and Actual Yield was for the Production Order, but every once in a while our production crew double enters the yield by mistake. I am trying to only take the last entry for each Production Order and then sum all the totals. If I SUM all it counts the duplicates which I dont want. 

 

My measure is taking the MAX entry number, in the total it is returning that MAX entry data rather than the SUM.

 

I hope I explained this well enough.

I see what you mean - have you considered creating a deduped version of the table then calculating for each row, eliminating the dedupe inside the calc? 

This took a while, but I was able to make it work this way. I feel as though there must be a formula that can be used, but I am happy it is working. Thank you for your suggestions!

Hi @Kfausch ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share the data sample and your desired output so that we could help further on it.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.