Forum Discussion
sumx with hierarchy
Sorry if this has been asked already but haven't been able to find anything.
I have a table that looks like this:
| Region | Branch | Item | Qty Requested | Qty Sent | Date |
| Region A | Branch 1 | Pizza | 10 | 9 | 1/1/22 |
| Region A | Branch 2 | Pizza | 10 | 12 | 1/1/22 |
| Region B | Branch 3 | Crotons | 5 | 4 | 1/1/22 |
| Region A | Branch 1 | Pizza | 5 | 10 | 1/7/22 |
| Region A | Branch 2 | Pizza | 10 | 6 | 1/7/22 |
| Region A | Branch 1 | Croutons | 10 | 5 | 1/7/22 |
| Region A | Branch 1 | Croutons | 10 | 7 | 1/8/22 |
I'm working on a drill down report to show fulfillment rate across the date range (slicer) at each level - region, branch and item. I have this formula
Which does a good job of getting the individual item fulfillment rates across the date slicer but the results are wrong at the branch and region level. For instance if Branch 1 is has 100 pizza requests, 150 pizza sends and 100 crouton requests with 60 crouton sends it shows the Branch 1 fulfillment rate as 100% since total qty sent>qty requested. It should show as 80% since the Branch 1 pizza rate is 100% and the crouton rate is 60%. I'd like this logic to roll up to the region level as well.
Let me know if this is doable and if you have any questions.
Thanks for your help! I've been stuck on this for awhile now
-Jon
You're grouping items together before dividing. Try calculating the ratio for each item and then averaging.
Fulfillment Rate = VAR Summary = ADDCOLUMNS ( SUMMARIZE ( Daily, Daily[Item], "@Sent", SUM ( Daily[Qty Sent] ), "@Requested", SUM ( Daily[Qty Requested] ) ), "@Ratio", MIN ( 1, DIVIDE ( [@Requested], [@Sent] ) ) ) RETURN AVERAGEX ( Summary, [@Ratio] )
2 Replies
- AlexisOlson
Super User
You're grouping items together before dividing. Try calculating the ratio for each item and then averaging.
Fulfillment Rate = VAR Summary = ADDCOLUMNS ( SUMMARIZE ( Daily, Daily[Item], "@Sent", SUM ( Daily[Qty Sent] ), "@Requested", SUM ( Daily[Qty Requested] ) ), "@Ratio", MIN ( 1, DIVIDE ( [@Requested], [@Sent] ) ) ) RETURN AVERAGEX ( Summary, [@Ratio] )Works great, thanks!
