Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi all!
I am working on a data set involving what number of items can fit inside a box. I am trying to make it so we can have variable box sizes. Here's my formula (Calculated Column)
Solved! Go to Solution.
@Thigs do you mean this?
Measure =
VAR _heightItem =
MAX ( 'item'[Height] )
VAR _lengthItem =
MAX ( 'item'[Length] )
VAR _widthItem =
MAX ( 'item'[Width] )
VAR _heightBox =
CALCULATE (
MAX ( box[Height] ),
FILTER ( box, box[Box Type] = ALLSELECTED ( box[Box Type] ) )
)
VAR _lengthBox =
CALCULATE (
MAX ( box[Length] ),
FILTER ( box, box[Box Type] = ALLSELECTED ( box[Box Type] ) )
)
VAR _widthBox =
CALCULATE (
MAX ( box[Width] ),
FILTER ( box, box[Box Type] = ALLSELECTED ( box[Box Type] ) )
)
VAR _cal =
INT ( DIVIDE ( _heightBox, _heightItem ) )
* INT ( DIVIDE ( _lengthBox, _lengthItem ) )
* INT ( DIVIDE ( _widthBox, _widthItem ) )
RETURN
_cal
pbix is attached
Hi @Thigs
Can you post sample data as text and expected output?
Not enough information to go on;
please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables
Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/
Hi! Thank you!
Here are the two tables I'm using -
This is the 'Box Dimensions' table :
Box Type | Length | Width | Height |
Small | 6 | 4 | 5 |
Medium | 8 | 6 | 6 |
Large | 10 | 8 | 7 |
And this is 'Item Dimensions'
SKU # | Item Name | Length | Width | Height | Weight | Custom Column - How Many Fit |
1001 | Apple | 6 | 5 | 5 | 2 | |
1002 | Orange | 5 | 4 | 4 | 1 |
I am trying to find the "How Many Fit" column. That is the output I am trying to find. I want to find out how many apples fit in each size box.
Basically, I want the user to be able to specify "Hey, we're using the small boxes" on a selection pane. Then the results will all populate with how many apples or oranges fit in the small boxes. If later the emploee decides that medium boxes should be used, they can change the selection to "medium boxes" on the pane and the graphics, charts, etc will update to show how many apples and oranges fit in a medium box.
I'm using this tutorial that shows how to make a variable selection pane -
How to Create Power BI TOP N Report (Variable N, Show Top 10/50/100…) - YouTube
Thanks so much for your help!
@Thigs do you mean this?
Measure =
VAR _heightItem =
MAX ( 'item'[Height] )
VAR _lengthItem =
MAX ( 'item'[Length] )
VAR _widthItem =
MAX ( 'item'[Width] )
VAR _heightBox =
CALCULATE (
MAX ( box[Height] ),
FILTER ( box, box[Box Type] = ALLSELECTED ( box[Box Type] ) )
)
VAR _lengthBox =
CALCULATE (
MAX ( box[Length] ),
FILTER ( box, box[Box Type] = ALLSELECTED ( box[Box Type] ) )
)
VAR _widthBox =
CALCULATE (
MAX ( box[Width] ),
FILTER ( box, box[Box Type] = ALLSELECTED ( box[Box Type] ) )
)
VAR _cal =
INT ( DIVIDE ( _heightBox, _heightItem ) )
* INT ( DIVIDE ( _lengthBox, _lengthItem ) )
* INT ( DIVIDE ( _widthBox, _widthItem ) )
RETURN
_cal
pbix is attached
I guess I replied too soon - this is working in a chart, but when I try to use it as values for a bar chart, it is blank. Any ideas?
THIS WORKED! Ah! I love it and I love you! Thank you so much!!!
I'm curious why you use the CALCULATE with FILTER and ALLSELECTED in your measure. Does it not work with just the MAX part?
As a side note, you can fit two apples if you rotate them to align Apple length with Box height. (The possibility of rotations is why I have the six cases in my answer.)
A calculated column cannot be responsive to slicers since they are only computed once when the model is loaded or refreshed (not in response to user interaction).
I recommend checking out this prior thread:
https://community.powerbi.com/t5/Desktop/Find-Biggest-Middle-and-Smallest-Value/m-p/1996228
Is there any way to get what I want? Where there is a way for the user to input what kind of box and it changes the report?
Yes. The post I linked to gives a solution.
Create this measure and it will adjust if you change the box type:
MaxFit =
VAR BoxH = SELECTEDVALUE ( Boxes[Height] )
VAR BoxW = SELECTEDVALUE ( Boxes[Width] )
VAR BoxD = SELECTEDVALUE ( Boxes[Length] )
VAR ItemH = SELECTEDVALUE ( Items[Height] )
VAR ItemW = SELECTEDVALUE ( Items[Width] )
VAR ItemL = SELECTEDVALUE ( Items[Length] )
VAR Case1 = TRUNC ( BoxH / ItemH ) * TRUNC ( BoxW / ItemW ) * TRUNC ( BoxD / ItemL )
VAR Case2 = TRUNC ( BoxH / ItemH ) * TRUNC ( BoxW / ItemL ) * TRUNC ( BoxD / ItemW )
VAR Case3 = TRUNC ( BoxH / ItemW ) * TRUNC ( BoxW / ItemH ) * TRUNC ( BoxD / ItemL )
VAR Case4 = TRUNC ( BoxH / ItemW ) * TRUNC ( BoxW / ItemL ) * TRUNC ( BoxD / ItemH )
VAR Case5 = TRUNC ( BoxH / ItemL ) * TRUNC ( BoxW / ItemH ) * TRUNC ( BoxD / ItemW )
VAR Case6 = TRUNC ( BoxH / ItemL ) * TRUNC ( BoxW / ItemW ) * TRUNC ( BoxD / ItemH )
RETURN
MAXX ( { Case1, Case2, Case3, Case4, Case5, Case6 }, [Value] )
While I inputted the formula correctly, I'm still getting blanks when I put it in a chart, graph, etc? Any ideas? I super appreciate your help!
What are you trying to chart? It works fine for me.
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |