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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
aartiladdha
Microsoft Employee
Microsoft Employee

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

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

Capture.PNG

11 REPLIES 11
Anonymous
Not applicable

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

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

Anonymous
Not applicable

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?

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
Not applicable

image.png

why are you saying that it's NOT 2172?

updated the post with Power BI snapshot 

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
Not applicable

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

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

Anonymous
Not applicable

yes you're right

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

Doesn't seem to work. 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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