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

[Ericwhv]

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!

[selimovd · Accepted 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.

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ericwhv]

@selimovd 

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!

[selimovd]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Ericwhv]

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

[selimovd]

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

[Ericwhv]

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

[selimovd · Accepted solution]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[selimovd · Accepted 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.

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic