Power BI aggregating data from one grain to overall week level / DAX Measures at a grain

[aartiladdha]

I have 3 measures. Inbounds, Outbounds and Net Avail.

Net Avail = Inbounds -Outbounds.

with this I have created another measure Non-Negative Net Avail = if (Inbound- Outbound) <0 then 0 else Inbound-Outbound.

This measure works fine when I am visualizing the data at Week, SKU, NON-Negative Net Avail level. However, when I remove SKU and view data at an overall level i.e Week, NON-Negative Net Avail, instead of summing up the variances at each SKU for that week, it just looks at the overall data for the week and zeros it out only when the overall for that week is negative.

See below table- I need the result at overall level for that week to be 2172 and not -1715.

Anothere xample from Power BI below. Left chart is the one without SKU and right one contains SKU. if we look at the data for a week say 10/28/2019, chhart on left shows 13.7K and on right shows 18.3K. Attaching excel for the same

https://drive.google.com/file/d/1XxeY-j4HnTBMIVSEO0mGVVaaOhvwzvdE/view?usp=sharing

[Anonymous]

Sorry, I don't get it. The behaviour looks (for the net avail) as expected. 

[aartiladdha]

edited the post. I am actually referring to the behaviour of NON-Negative Net Avail.

[Anonymous]

but the non-negative IS 2172. You said it has to be 2172.

So are you showing in the screenshot the expected result or what you see in powerbi?

Can you show what you see in powerbi then?

[aartiladdha]

So, the non-negative should be coming out to be 2172.  But it's actually coming out to be 0 as (2580-4295) is negative.

[Anonymous]

why are you saying that it's NOT 2172?

[aartiladdha]

updated the post with Power BI snapshot 

[aartiladdha]

the moment I remove SKU from the graph, it switches to -1715 because it calculates the measure at week level and not SKU. I have created the tabel in excel to show that the expected result with the current measure is 2172. that works only when I pull SKU in the graph. If I am removing SKU, the measure does  (2580-4295) for that week to give -1715

[Anonymous]

Ok so powerbi is correctly calculating your non-negative at SKU level, but you want a different behaviour. You want the sum of the non-negative at SKU level.
So you need to iterate on each SKU for that week, calculate the value and then sum.

So I'm assuming that your sku table is called "skutable" and the column of the sku is called "skuvalue"
NonNegativeSumBySku=SUMX(
                  skutable[skuvalue];
                  CALCULATE([Non Negative Net Avail])
)

this SHOULD do the trick

[aartiladdha]

doesn't let me select the skuvalue. It's throwing an error when I select the column name. 

[Anonymous]

yes you're right

use just "skutable" and not "skutable[skuvalue]"

[aartiladdha]

Doesn't seem to work.