Calculation of only positive values out of different sources

[Anonymous]

I want to calculate the quantity we have to produce for a defined year.
Everything works fine until I want to summarize only the positive values out of the row "Need".
I always get the same value for "Production" as for "Need".
I checked already the relationships. Everything is correct.
NO IDEAOutput TableMeasuresWIP Table

[Anonymous]

Hi,@Anonymous I am glad to help you.
Hello,@PhilipTreacy,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.

You only need a little change to realize your needs, before the measure [Need] calculation error is due to the calculation of the measure depends on the current computing environment, each line of the computing environment may be different, for the calculation of the Total line of the logic is to calculate the total value of the measure directly on them As shown in the figure 103 + 205-285-194=-171
So you need to Use HASONEVALUE() or ISINSCOPE() to determine the hierarchy of the current MEASURE calculation

Make changes to your measure [Need].

like this

result_Need = 
IF (
    ISINSCOPE ( Backlog[Material nbr] ),
    [Need],
    SUMX ( FILTER ( 'Backlog', [Need] > 0 ), [Need] )
)
//Use HASONEVALUE()or ISINSCOPE() determine the hierarchy of the current MEASURE calculation

Here is my test data:

M_Backlog = SUM('Backlog'[Quantity])

M_Provisions = SUM('Provisions'[Quantity])

M_Inventory = SUM('Inventory'[Quantity])

WIP = SUM('Last_Value_Table'[Last Quantity])



Need = [M_Backlog]+[M_Provisions]-[M_Inventory]-[WIP] 

Luckily I found similar posts that have been resolved.
URL:
Solved: Re: Incorrect total - Microsoft Fabric Community

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

[Anonymous]

Hey, 
I still have the problem. Here some more Data.

I have three files with a timeline (ID for storage date), so I have to select the last day to get the current values.
When I compare the different Need-Calculation with Excel, Need 3 delivers the correct values per line, but the sum isn't correct.

I tried several scenarios, but nothing helps to solve the problem...

It seems that have a basic problem. I would be great, if you can help.

Best Regards

Benno



ModellNeed 2Need 3Need 4
ResultFilter

Excel calculation

[Anonymous]

Hi,@Anonymous .Thanks for your reply
In fact I noticed that your measure: _Need_3 directly returns the variable value quantity in the IF judgment, which is not a measure and may cause problems when returned directly.

Here is my test, I hope it will help you!

_Need_3:

You need to modify your code according to your actual data

_Need3_Change = 
    SUMX ( FILTER ( 'Backlog', [Need] > 0 ), [Need] )
    //Please don't use var variables to put in sumx as a condition, it may cause problems, the correct way is to use the measure [Need] directly.
    //Need = [M_Backlog]+[M_Provisions]-[M_Inventory]-[WIP]. 
    
//or use function:ISINSCOPE


_Need3Change2 = 
IF(
    ISINSCOPE('Backlog'[Material nbr]),SUMX(FILTER('Backlog',[Need]>0),[Need]),
    SUMX(FILTER('Backlog',[Need]>0),[Need])
)
// Don't use the var variable approach, pass the variable directly into the sumxx function, and try to use MEASURE as a judgment and operation condition.

Don't use IF judgment and then return quantity directly, because it may not work for Total judgment.

It is best to use the same ISINCOPE function to determine whether the current total column, if it is a total column, the result of the return of the sum aggregation, rather than directly return the "quantity", because the total is theoretically a collection of value storage

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

[Anonymous · Accepted solution]

Perfect! Now it works.
Thanks a lot for the support!

My last mistake (use of the solution above) was the use of the wrong filter definition….OMG

Here my lessons learned:

1. Don't use 'VAR' in the measure for this context, because the sum will be wrong (Need_5)
2. Don't use a simple 'IF' function. The sum will be wrong (Need_1)
3. It works with a measure with 'SUMX' and the correct filter definition (Need_3)
4. You can improve the measure by using 'ISINSCOPE' (Need_2) or 'HASONEVALUE' (Need_4)

Again, thanks for the excellent support.

[Anonymous]

Hi,@Anonymous .Thank you for your reply.
You are welcome.

[PhilipTreacy]

@Anonymous 

I'm confused.  Doesn't Try2 for Producton give you what you want?  What is it that you actually want if it does not?

Phil

[Anonymous]

Hi,@Anonymous .
May I ask if the advice I provided was helpful?
If it helped you can mark the suggestions that helped you as solutions or share your solutions in the forum.
If your problem is not yet solved, could you provide more non-sensitive information. It would be great if you can share the PBIX file which does not contain sensitive data, it would be helpful in solving your issue.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thats exactly my problem.
I tested the calculation and created therefore a table.
When is use the second calculation I got always the total sum in the table for all lines.

Table 4