Condition (IF/SWITCH) - assignment of the production batch number

DAX DAX Calculations
1

Hi to All,

I am asking you for help in referenec to my PBI report.
My problem concerns transfer some requirements that so far I was able to settle with VBA sub or formulas in Excel.
The problem is probably trivial for most of you, so I will appreciate any help.

I would like to assign production series to part numbers based on number of pieces produced in lookup table.
If cum quantity on first orders is <= 5 pcs, the orders should go to the 1st group, then to the 2nd group if cum qty <= 15, then to the 3rd group if the cum quantity is <25, all others should be classified as Other.

The idea is that the first 5 units for each part, then another 10, and next 10 should be classified as separate production patches (1,2,3) the rest as other regardless the mnfg. pieces.

This case can be resolved as a calculated column.
production batch _forum.pbix (26.3 KB)

In Excel, this can be solved with the additional column with cummulative quantity for each part number. You can see it in the screenshot below.

image
image268Ă—591 5.86 KB

Thank you in advance,
Kamil

2

Hello @Kamil,

Thank You for posting your query onto the Forum.

In order to achieve the results based on the condition that you’ve specified. Below are the measures alongwith the screenshot of the final results provided for the reference -

Cumulative Quantities = 
CALCULATE( SUM( Tabela1[Quantity] ) ,
    FILTER( ALLEXCEPT( Tabela1 , Tabela1[Index] ) ,
        ISONORAFTER( Tabela1[Order No.] , MIN( Tabela1[Order No.] ) , DESC ) ) )

Batch Evaluation = 
SWITCH( TRUE() , 
    [Cumulative Quantities] <= 5 , "1st Batch" , 
    [Cumulative Quantities] >= 6 && [Cumulative Quantities] <= 15 , "2nd Batch" , 
    [Cumulative Quantities] >= 16 && [Cumulative Quantities] <= 25 , "3rd Batch" , 
    "Others" )

Final Results
Final Results1055Ă—1092 126 KB

I’m also attaching the working of the PBIX file for the reference purposes.

If want to calculate the results in a calculated column, it’s showcased inside the PBIX file.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

production batch _forum - Harsh.pbix (31.7 KB)

2 Likes
3

Hello @Harsh
Thank you for your great support.
I really appreciate this.

What if the informations in the Order No. do not have ascending order?
How else can you calculate Cumulative Quantities, because at this point we’re using this column in step:

 Tabela1[Order No.] <= EARLIER( Tabela1[Order No.] ) ) )
4

Hello @Kamil,

In the data, eventhough if the information is not sorted in the ascending order, “EARLIER()” function will evaluate the results in ascending order.

In the below provided screenshot, you’ll observe that although the Sub-Category is not in a proper order but the results evaluated for the “Cumulative Amounts” take the proper order into the account.

Results

Thanks and Warm Regards,
Harsh

1 Like
5

Hello @Harsh
I have one more question:

If we take a look on your data sampleI if I would like to get the result as in the screenshot below, how should a formula be written in a calculated column?
In fact, the (Sub Category) condition could be omitted and the cumulative value should be based on the Category column (and maybe on virtual row number per category / sth like sql partition by)

image
image1035Ă—349 38.5 KB

6

Hello @Kamil,

In that case, a ranking/index column per category will be required to be added inside the table using “Power Query” since we don’t want the results in an ascending order but want to evaluate the results in as is condition of the table.

So this is how the table will look like after adding the Ranking/Index column inside a table and then rather than referencing “Order No” column inside the formula we’ll now reference “Ranking/Index” column. Below is the screenshot of the final results provided for the reference -

Final Results
Final Results703Ă—452 16.8 KB

I’m also attaching the working of the PBIX file as well as providing a link below of a video which shows how to create a “Index/Ranking Column Per Category” inside the Power Query.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Cumulative Totals - Harsh.pbix (23.3 KB)

1 Like