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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Wrong total sum in If then measure or switch measure calculation - really need help

Hi Community, I have searched so many solutions but none of them could fix the issue I have(or maybe it is too confusing to understand). I have seen others post similar issues, I tried the solutions from the answers, but still not work.

Here is the screenshot of my calculation.

I need to get the fulfillment qty based on BO order quantity and scheduled qty per week.

for example, total order quantity is 90, in week 1 it schedules 420, so the week 1 fulfilled qty will be 90(max qaunitty that is needed or could have). I tried both "if then statement" and "switch function".

All total sum are showing wrong in this case. Row level is correct.

1 week fulfill qty total should equal 90+288 = 378, but it shows 708.

Any one could explain or bring a solution? many many thanks!!!!! I have worked on this issue for more than 6 hours. ;(
pbittt.PNG
 

2 ACCEPTED SOLUTIONS
mahoneypat
Employee
Employee

Test this expression to get the correct total for your IF measure, replacing Table with the actual name of the table.

NewMeasure - SUMX(VALUES(Table[SKU_PLANT), [1 Week Fulfill Qty])

This should give the same values in the rows, but the correct sum in total.

If this works for you, mark it as the solution. Praise is also appreciated. Please let me know if you don't.

Best regards

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Icey
Community Support
Community Support

Hello @freshpeper ,

In the visual, each row, including the total value row, will apply the set calculation logic separately.

The total value of [WEEK FULFILL QTY] is to get as shown below:

pbittt.PNG

Please try to use the measurement @mahoneypat provided. He can give whatever you want.

Best regards

Icey

If this post helps,then consider Accepting it as the solution to help other members find it more quickly.

View solution in original post

5 REPLIES 5
Icey
Community Support
Community Support

Hello @freshpeper ,

In the visual, each row, including the total value row, will apply the set calculation logic separately.

The total value of [WEEK FULFILL QTY] is to get as shown below:

pbittt.PNG

Please try to use the measurement @mahoneypat provided. He can give whatever you want.

Best regards

Icey

If this post helps,then consider Accepting it as the solution to help other members find it more quickly.

mahoneypat
Employee
Employee

Test this expression to get the correct total for your IF measure, replacing Table with the actual name of the table.

NewMeasure - SUMX(VALUES(Table[SKU_PLANT), [1 Week Fulfill Qty])

This should give the same values in the rows, but the correct sum in total.

If this works for you, mark it as the solution. Praise is also appreciated. Please let me know if you don't.

Best regards

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


lbendlin
Super User
Super User

You may want to show the definition of [Today BO QTY] and [1 WEEK QTY]. Measures can impact measures.

Anonymous
Not applicable

@lbendlin  Thank you for asking that. Here are two measures:
 
Today BO QTY =
var _today = MAX(Dailydata[Time Period])
return
CALCULATE(SUM(Dailydata[BO Quantity]),Dailydata[Time Period]=_today)
 
1 WEEK QTY = CALCULATE(SUM(SAPexport[Scheduled Quantity]),SAPexport[Delivery Designation]="1 WEEK")

The way I understand it (I am starting to learn about filter context) your [Today BO QTY]  measure has no filter modifiers. It is equivalent to 

 

Today BO Qty := sum(DailyData[BO Quantity])

 

See if you can confirm that, and if yes, decide if that was what you wanted to achieve. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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