sumx with hierarchy

Eth — Wed, 02 Feb 2022 21:20:26 GMT

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

Fulfillment Rate = if(SumX(VALUES(Daily[Item]),CALCULATE(Sum(Daily[Qtyt Sent])))/SumX(VALUES(Daily[Item]),CALCULATE(Sum(Daily[Qty Requested])))>.99, 1, SumX(VALUES(Daily[Item]),CALCULATE(Sum(Daily[Qtyt Sent])))/SumX(VALUES(Daily[Item]),CALCULATE(Sum(Daily[Qty Requested]))))

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

Re: sumx with hierarchy

AlexisOlson — Wed, 02 Feb 2022 22:45:04 GMT

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] )

Re: sumx with hierarchy

Eth — Thu, 03 Feb 2022 13:32:41 GMT

Works great, thanks!

topic Re: sumx with hierarchy in DAX Commands and Tips

sumx with hierarchy

Re: sumx with hierarchy

Re: sumx with hierarchy