cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ericwhv
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 Number204040H
AB12345111111 1 
AB12345222222 1 
AB12345333333 1 
AB5678999999911 
AB5678988888811 

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
selimovd
Community Champion
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:

selimovd_0-1620423224185.png

Also if you would add the PO Number to the table the total works:

selimovd_1-1620423333171.png

 

 

If you just want to see the MAX number you can also do it with the MAX function:

1.gif
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
 

View solution in original post

selimovd
Community Champion
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:

selimovd_0-1620982940443.png

 

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
 

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User III
Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

@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
Community Champion
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
 
Ericwhv
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!!!

 

selimovd
Community Champion
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

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

selimovd
Community Champion
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:

selimovd_0-1620982940443.png

 

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
 

View solution in original post

selimovd
Community Champion
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:

selimovd_0-1620423224185.png

Also if you would add the PO Number to the table the total works:

selimovd_1-1620423333171.png

 

 

If you just want to see the MAX number you can also do it with the MAX function:

1.gif
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
 

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

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

PowerPlatform 768x460.png

Microsoft Learn

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

Top Solution Authors