Don't sum up subtotal if the value have childs

[Anonymous]

Hi,

I'm trying to spread Production consumption and show it in Matrix visual.

Produced product - LLPV-2362 (Level 1), it is manufactured by using II level products, and some of Level II product also has its consumption (III level) and so on.

The problem is that PBI sums up next level qty, but I need that in the first level it will show qty that was used for that level consumption.

For example:

Material LLPV-2067 is used in LLPV-2362 production, and it has it's on consumption.

For LLPV-2362 is used just 477 units in production, but BI sums up everything and shows 39 794 units.

How can I change Hierarchy or data, to get this result (LMPM-0086 can be sum up):

 This is an example with one Production order, please take in mind that there will be plenty of them. The example file lies here: 

https://drive.google.com/open?id=1aKzUhQsxX29qnlfVq6UrPhYnXXRXqIi2

Any ideas are appreciated.

[Anonymous]

Ok, I got an idea, can someone help me to write dax?

If the material has an initial order (Initial order = true), then bring the sum of quantity where the lowest level is Blank, otherwise - the sum of quantity.

[dax · Accepted solution]

Hi rimgri,

You could try below measures

Measure = CALCULATE(SUM(Production[Quantity]),FILTER('Order Hierarchy','Order Hierarchy'[III level]=BLANK()))

Best Regards,
Zoe Zhi

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

[Anonymous]

Thanks, @dax ,

Quite a simple solution, but there is a problem, it counts just in one level, but there is data in the third and fourth levels:

[Anonymous]

I think I found a solution:

Quantity = IF(
ISBLANK(SELECTEDVALUE('Order Hierarchy'[III level])); 
CALCULATE(SUM(Production[Quantity]);FILTER('Order Hierarchy';'Order Hierarchy'[III level]=BLANK()));
        IF(
            ISBLANK(SELECTEDVALUE('Order Hierarchy'[IV level])); 
            CALCULATE(SUM(Production[Quantity]);FILTER('Order Hierarchy';'Order Hierarchy'[IV level]=BLANK())); 
SUM(Production[Quantity])))

@dax  can I ask you to look through the formula, maybe you have something to add?

[dax]

Hi rimgri,

Thank you for your sharing! I test this in your sample(because my language is English, so I need to change";" to ","), it will work well  in your sample, I think this is correct, there is nothing need to add currently.

And it seems that you solve your problem, if so and if you'd like to, you could mark corresponding post as answer or share your solutions. That way, people who in this forum and have similar issue will benefit from it.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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