Trouble with DAX and filter context

Guitarmageddon

Board Regular
Joined
Dec 22, 2014
Messages
161
Hello all, so I have a long list of SKUs and the purchase orders they have been ordered on. I'm getting a sense of SKU-level fill rate percentage.

I have added a measure for distinct count of items that the purchase orders were on. I'm trying to take it a step further : when I expand out the "style desc" column as you can see in row 8, how can I get the DAX to tell me the fill rate math (qty received divided by qty ordered) for the TOTAL parent PO, irrespective of whatever SKU I am looking at. So for example, when I expand out column C like Ive done here, it would show me the total fill rate for ALL items that PO had seen. Hopefully that makes sense. So basically, while I can see that PO 2260994 had a fill rate of 200% on that specific item, as a whole for all SKUs ordered,the PO fill rate could have been 50% lets say.


note: fill rate , distinct PO, are dax measures. The rest is source data.
1612884953988.png


and for a quick glimpse of source data, its like this
1612885241905.png
 

Attachments

  • 1612884874973.png
    1612884874973.png
    147.1 KB · Views: 6

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
I tried this , but obviously wrong, gave me an error. I think im sort of on the right track but cant get it
=CALCULATE(
[Sum of Qty Received]/[Sum of Qty Ordered],
FILTER(
Table1,Table1[PO ID])

EDIT: also, in doing some research, it looks like they do something similiar in this example, if you scroll down to the bottom screen shot. The distinction between revenue by total country vs region.
 
Last edited:
Upvote 0
ok i think i got something! Can I get someone to sanity check the method in case there is a better way?

I did t his method below. Basically used "ALLEXCEPT" which, as I understand, preserves all the filters I have set on the columns EXCEPT for anything placed on the PO. So basically, the total fill rate on ALL the orders and styles is 69% as you can see copied down, which is technically correct. But when I enter into an individual style, I can now see not just how that specific Purchase order/Style combo filled, but also the TOTAL fill for that PO across all the styles.
1612886975618.png
 
Upvote 0

Forum statistics

Threads
1,223,732
Messages
6,174,182
Members
452,550
Latest member
southernsquid2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top