Get Total for Measure?

[Thigs]

Hi all!

What I've got going on is a list of Aisles (1 - 14) that had a certain number of SKUs bought from each (count('Sample Black Friday'[SKU])). I have a maximum that can be pulled from each aisle due to machinery limitations (SELECTEDVALUE('Lift Capacity'[Lift Capacity])

I have a table visual with the aisles and then the counts of the SKUs and the remainders as columns. What I want to do is have a total/sum of all the remainders, regardless of aisle. So if Aisle 1 has 30 remainders and Aisle 2 has 3, then the total remainders should be 33. Then I want to take the remainders and divide them into the aisles that have no remainders. So if Aisle 8 has no remainders, it can get a few more SKUs, etc. Can anyone think of how to do this? Here is my current formula - 

Test =

VAR OriginalCount = count('Sample Black Friday'[SKU])

VAR Remainder = if(OriginalCount > SELECTEDVALUE('Lift Capacity'[Lift Capacity]), OriginalCount - SELECTEDVALUE('Lift Capacity'[Lift Capacity]))

VAR NumberOfAisles = 14

RETURN

Remainder

My thought is to create another variable that is number of Aisles that have remainders with an If statement, then return "Remainder / (NumberOfAislesWithoutRemainders)" to get what portion goes into the rest of the aisles. Does that make sense? Any help is greatly appreciated!

[Anonymous]

Hi @Thigs ,

Have trouble to follow, please share some sample data and expected result.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 

Best Regards,

Jay

[Thigs]

Hi! Thanks so much!

Here is a link to my PBIX file - 

https://www.dropbox.com/s/m433iqu9u1o5a5f/Aisle%20Simulation%20Non-Identifying.pbix?dl=0

You can see what I want in the "Test" column - it's 35, but what I want is to show that remainder for all rows, not just the row that has the remainder, if that makes sense. What I want as an end result is to essentially reallocate that remainder to the other aisles that have more room.