Recursive calculation Food supply

Power Query
1

Hello, attached a XLSX file with 4 tabs , for simplying reason I have put them in a single file.
the problem is food distribution for poor people

  • member list ( tab)
    this weekly list the people that are entitled for the products ( listed in the matrix )
    the KEY code is related with the matrix

  • products (tab ) ( the list of possible products to distribute )

  • Target (tab ) the list of available product for a particular week

  • matrix (tab ) the number of products for each KEY and product for that week

how to calculate (tab ) you will see that according the matrix I will need more product ( example Milk 1228 ) than there is available target 1050
therefore I have to adjust the matrix to get close to the target
for the moment this is done manually ( see tab )
any help is greatly appreciated
Roger
RECURSIVE calculation POWER BI or EXCEL.xlsx (69.1 KB)

2

Hi @Roger - Thanks for sharing the file and explaining the calculation. Have few questions.

  1. In Calculation sheet, you have selected the keys in particular order like below. Is there any required criteria for this.

1N
2N
3N
4N

  1. For keys 4N, 5N, there is value of “4” but that is not considered for reduction, what is the criteria to consider.

Thanks
Ankit J

3

thanks for looking at this problem,
the order I choose is the “total per key” (column L) starting with the KEY *N and the highest number for that key
( they have no childern and the priority is the family with childern *Y the 4N and 5 N have only 4 as total so I skip them and start with the high number ending with *Y until I get below the target.
hope this makes sense
kind regards
Roger

4

Ankit
if it is simplier for the solution we can rank the KEY # from 1N …10Y
Roger

5

Hi @Roger - I have tried to come up with attached solution. Do check if it is helpful. I have created a Index Column based on the keys from Matrix.

For information, I can’t skip values for “4N and 5N” considering solution needs to be dynamic.

Primarily entire calculations id done on Matrix table. From Members, I have only taken Members Count for each key and Qty available from Target.
Recurrsive.pbix (54.2 KB)

Thanks
Ankit J

6

Hello again, thanks for the pbix and the time you spent in solving this problem. It’s more complicated,
each member on the members list must receive at least 1 product that is on the matrix list. in your table the value for 3Y is empty
I have attached the xlsx file with some more info check the tab MATRIX ( explains the path to folow ) check the pivot for milk and pivot for rice ( for the expected result ) and check the tab desired result ( the individual list of products I finally need )

thanks in advance
Roger

RECURSIVE calculation POWER BI or EXCEL version 1.xlsx (346.4 KB)

7

Hi @Roger - Already spend too much time on this. Will check if gets time.

Note for future: Please share all requirements when raising a request as even a single change can impact the entire solution.

Thanks
Ankit J

8

Hi @Roger - Have tried but solution is becoming too complex and also will not achieve end results as it will need manual work.

Will gave a try to simplify the solution this weekend else need to wait if someone else can help.

Thanks
Ankit J

9

FOOD DISTRIBUTION FOR ACTIEMIN ( for DNA).pbix (124.9 KB)

1 Like
10

found a solution for the recursive calculation , first create a function to calculate the adjusted quantity, then wrapped it in a query to run for each prod -ID ( see adjusted quantities for each member )

1 Like