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

[Anonymous]

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. ;(

 

[mahoneypat · Accepted solution]

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

[Icey · Accepted solution]

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:

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 · Accepted solution]

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

[lbendlin]

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

[Anonymous]

@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")

[lbendlin]

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.