cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Thigs
Helper III
Helper III

Get Total for Measure?

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(OriginalCountSELECTEDVALUE('Lift Capacity'[Lift Capacity]), OriginalCountSELECTEDVALUE('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!
2 REPLIES 2
v-jayw-msft
Community Support
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

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. 

Helpful resources

Announcements
Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Solution Authors
Top Kudoed Authors