Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Thank you everyone in advance
I have the data as below
Booking Number | PO Number | 20 | 40 | 40H |
AB12345 | 111111 | 1 | ||
AB12345 | 222222 | 1 | ||
AB12345 | 333333 | 1 | ||
AB56789 | 999999 | 1 | 1 | |
AB56789 | 888888 | 1 | 1 |
the Data is based on the PO number level, that's why we have the same booking number in place.
at the right side, is the number of box required.
Example: Booking# AB12345, the number of box required is "1 x 40" instead of 3 x 40(1+1+1)
Booking# AB56789, the number of box required is 1 x 20 + 1 x 40 instead of 2 x 20 + 2 x 40
So, how can i show the the correct number of box required as a measurement?
for example, both booking number of box required will be 2 x 40 + 1 x 20
Thank you!
Solved! Go to Solution.
Hey @Ericwhv ,
you can either solve it with a measure of you can do it the lazy way by just taking the MAX value.
The measure for the 20 would be:
Amount 20 =
VAR vTable =
ADDCOLUMNS(
VALUES( myTable[Booking Number ] ),
"@Max",
CALCULATE(
MAX( myTable[20] )
)
)
VAR vTotal =
COUNTX(
vTable,
[@Max]
)
RETURN
IF(
ISINSCOPE( myTable[Booking Number ] ),
MAX( myTable[20] ),
vTotal
)
And the same for the 40, but different column:
Amount 40 =
VAR vTable =
ADDCOLUMNS(
VALUES( myTable[Booking Number ] ),
"@Max",
CALCULATE(
MAX( myTable[40] )
)
)
VAR vTotal =
COUNTX(
vTable,
[@Max]
)
RETURN
IF(
ISINSCOPE( myTable[Booking Number ] ),
MAX( myTable[40] ),
vTotal
)
The result will then be:
Also if you would add the PO Number to the table the total works:
If you just want to see the MAX number you can also do it with the MAX function:
Be aware with this method the Total shows only 1.
Hey @Ericwhv ,
sure, I will break the formula down.
With the first variable vTable I take all the distinct booking numbers (VALUES( myTable[Booking Number ] )) on the rows and add a column [@Max] with the MAX value of the column [40]. Like this I get rid of all the duplicates:
I just realize that this could also have been solved with a SUMMARIZE. This would be a little more elegant.
With the next Step COUNTX I iterate through this table and count the amount of entries. So for Booking Number AB12345 there would be a 1, for the total a 2 as the filtered table has 2 entries.
In the last step I check if the Booking Number is in scope (so in the row) or if it's the Total row and return either the MAX or the value from the row.
So while writing this, I realized that I was doing the formula waaaaay tooo complicated. Sorry about that, I guess I was tired on Friday 😅😉
You can achieve the same by just removing the duplicates for Booking Number and the Value and then do the sum. So I would now recommend the following formula:
For the 20:
Amount 20 V2 =
VAR vTable = SUMMARIZE(myTable, myTable[Booking Number ], myTable[20])
RETURN
SUMX(vTable, myTable[20])
For the 40:
Amount 40 V2 =
VAR vTable = SUMMARIZE(myTable, myTable[Booking Number ], myTable[40])
RETURN
SUMX(vTable, myTable[40])
Sorry about that.
About the questions you mentioned below. If you already have a measure you don't need to use CALCULATE because you're not changing the filter context.
Just add or multiply the measures:
Total in T = [Amount 20]*1 + [Amount 40]*2 + [Amount 40H]*2
Hi,
You may download my PBI file from here.
Hope this helps.
thank you for your help!
when i try to add your measurement in there, here is the warning msg
The ISINSCOPE function expects a column reference expression for argument '1', but a table reference expression was used.
can you plesae have a look for me please, thank you!
Hey @Ericwhv ,
that error message means that you used a table instead of a column for the INSCOPE function.
In my function for example I refer to the column:
ISINSCOPE( myTable[Booking Number ] )
You would get that error message mentioned when you just refer to the table like that:
ISINSCOPE( myTable )
Please check when you pasted my formula, if you really used the reference for the column [Booking Number].
@selimovd last question, if i would like to base on this measurement to find out how many TEU per shipment
how can i do it?
i try to use
Hey @Ericwhv ,
you don't need CALCULATE in this case. You only need CALCULATE if you modify the filter context. For example if you want to exclude or include a specific value, change the date, remove filters or explicitly set a filter value for a measure. You don't do any of that, so you don't need to use CALCULATE.
Try the same without CALCULATE:
Total in T = [Amount 20] * 1 + [Amount 40] * 2 + [Amount 40H] * 2
Let me know if that works.
Best regards
Denis
Thank you, that is really helpful!!
if you don't mind, can you please explain the logic behine to me?
i dont really understand the last two step, countX and isinscope that part, thank you!!
Hey @Ericwhv ,
sure, I will break the formula down.
With the first variable vTable I take all the distinct booking numbers (VALUES( myTable[Booking Number ] )) on the rows and add a column [@Max] with the MAX value of the column [40]. Like this I get rid of all the duplicates:
I just realize that this could also have been solved with a SUMMARIZE. This would be a little more elegant.
With the next Step COUNTX I iterate through this table and count the amount of entries. So for Booking Number AB12345 there would be a 1, for the total a 2 as the filtered table has 2 entries.
In the last step I check if the Booking Number is in scope (so in the row) or if it's the Total row and return either the MAX or the value from the row.
So while writing this, I realized that I was doing the formula waaaaay tooo complicated. Sorry about that, I guess I was tired on Friday 😅😉
You can achieve the same by just removing the duplicates for Booking Number and the Value and then do the sum. So I would now recommend the following formula:
For the 20:
Amount 20 V2 =
VAR vTable = SUMMARIZE(myTable, myTable[Booking Number ], myTable[20])
RETURN
SUMX(vTable, myTable[20])
For the 40:
Amount 40 V2 =
VAR vTable = SUMMARIZE(myTable, myTable[Booking Number ], myTable[40])
RETURN
SUMX(vTable, myTable[40])
Sorry about that.
About the questions you mentioned below. If you already have a measure you don't need to use CALCULATE because you're not changing the filter context.
Just add or multiply the measures:
Total in T = [Amount 20]*1 + [Amount 40]*2 + [Amount 40H]*2
Hey @Ericwhv ,
you can either solve it with a measure of you can do it the lazy way by just taking the MAX value.
The measure for the 20 would be:
Amount 20 =
VAR vTable =
ADDCOLUMNS(
VALUES( myTable[Booking Number ] ),
"@Max",
CALCULATE(
MAX( myTable[20] )
)
)
VAR vTotal =
COUNTX(
vTable,
[@Max]
)
RETURN
IF(
ISINSCOPE( myTable[Booking Number ] ),
MAX( myTable[20] ),
vTotal
)
And the same for the 40, but different column:
Amount 40 =
VAR vTable =
ADDCOLUMNS(
VALUES( myTable[Booking Number ] ),
"@Max",
CALCULATE(
MAX( myTable[40] )
)
)
VAR vTotal =
COUNTX(
vTable,
[@Max]
)
RETURN
IF(
ISINSCOPE( myTable[Booking Number ] ),
MAX( myTable[40] ),
vTotal
)
The result will then be:
Also if you would add the PO Number to the table the total works:
If you just want to see the MAX number you can also do it with the MAX function:
Be aware with this method the Total shows only 1.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |