topic Re: Get Total for Measure? in DAX Commands and Tips

Get Total for Measure?

Thigs — Tue, 28 Jun 2022 13:23:10 GMT

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!

Re: Get Total for Measure?

Anonymous — Fri, 01 Jul 2022 09:35:53 GMT

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

Re: Get Total for Measure?

Thigs — Wed, 06 Jul 2022 20:10:10 GMT

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.