cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

## how can I get the correct "number of box required"

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!

2 ACCEPTED SOLUTIONS
Community Champion

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.

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Community Champion

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``

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

8 REPLIES 8
Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Frequent Visitor

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!

Community Champion

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].

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Frequent Visitor

@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

Total in T = CALCULATE([Amount 20]*1+[Amount 40]*2+[Amount 40H]*2)
which is not work
1 x 20 = 1 TEU
1 x 40 = 2 TEU
do you have any idea for me, thank you so much!!!

Community Champion

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

Frequent Visitor

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!!

Community Champion

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``

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Community Champion

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.

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

## Helpful resources

Announcements

#### Manage your user group events

Check out the News & Announcements to learn more.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

#### Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Top Kudoed Authors