How to multiply a fix value with a column value based on multiple filters

[dnsia]

Hi all, 

I need a DAX measure for below scenario. I prefer to use DAX than calculated column as I have a big volume of data for this report. 

Scenario:

If load column or discharge column has a value of either Port A, B or C, the sum of its Freight and Bunker Surcharge needs to be multiplied by 5% to get the commission amount. 

Data I need : 

Load Port		Discharge Port		Freight		Bunker Surcharge		Commission
PORT A		PORT D		3500		1200		235
PORT A		PORT D		3500		1200		235
PORT A		PORT D		3500		1200		235
PORT B		PORT D		21556.65		600		1107.8325
PORT B		PORT D		10419.43		600		550.9715
PORT C		PORT D		12822.71		0		641.1355
PORT C		PORT D		12822.71		0		641.1355
PORT C		PORT D		12822.71		0		641.1355
PORT C		PORT D		12822.71		0		641.1355
PORT D		PORT A		18206.51		0		910.3255
PORT D		PORT A		21000		1200		1110
PORT D		PORT A		21000		1200		1110
PORT D		PORT A		21000		1200		1110
PORT D		PORT C		12909.86		1200		705.493
PORT D		PORT C		12909.86		1200		705.493
PORT L		PORT C		10997.36		400		569.868
PORT L		PORT C		8312.79		400		435.6395
PORT D		PORT B		16966.07		0		848.3035
PORT D		PORT B		21205.14		0		1060.257
PORT E		PORT B		8518.01		600		455.9005
PORT E		PORT B		8518.01		600		455.9005
PORT M		PORT L		9600		300		0
PORT M		PORT Z		7400		560		0

Thank you!

Regards,
Dina

[DataInsights · Accepted solution]

@dnsia,

Try these steps:

Total Bunker Surcharge = SUM ( Port[Bunker Surcharge] )

Total Freight = SUM ( Port[Freight] )

Commission = 
VAR vBaseTable =
    ADDCOLUMNS (
        SUMMARIZE ( Port, Port[Load Port], Port[Discharge Port] ),
        "tmpFreight", [Total Freight],
        "tmpBunkerSurcharge", [Total Bunker Surcharge]
    )
VAR vCommissionTable =
    ADDCOLUMNS (
        vBaseTable,
        "tmpCommission",
            VAR vLoadPort = Port[Load Port]
            VAR vDischargePort = Port[Discharge Port]
            RETURN
                IF (
                    vLoadPort IN { "Port A", "Port B", "Port C" }
                        || vDischargePort IN { "Port A", "Port B", "Port C" },
                     ( [tmpFreight] + [tmpBunkerSurcharge] ) * .05,
                    0
                )
    )
VAR vResult =
    SUMX ( vCommissionTable, [tmpCommission] )
RETURN
    vResult

[v-yangliu-msft · Accepted solution]

Hi  @ dnsia,

Here are the steps you can follow：

1. Create a calculated column.

Commission =
IF('Table'[Load Port ]="PORT A" ||'Table'[Load Port ]="PORT B" ||'Table'[Load Port ]="PORT C" ||'Table'[Discharge Port]="PORT A" ||'Table'[Discharge Port]="PORT B" || 'Table'[Discharge Port]="PORT C",
('Table'[Freight]+'Table'[Bunker Surcharge])*0.05,
0)

2. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dnsia]

This works as well. Thank you!

[DataInsights · Accepted solution]

@dnsia,

Try these steps:

Total Bunker Surcharge = SUM ( Port[Bunker Surcharge] )

Total Freight = SUM ( Port[Freight] )

Commission = 
VAR vBaseTable =
    ADDCOLUMNS (
        SUMMARIZE ( Port, Port[Load Port], Port[Discharge Port] ),
        "tmpFreight", [Total Freight],
        "tmpBunkerSurcharge", [Total Bunker Surcharge]
    )
VAR vCommissionTable =
    ADDCOLUMNS (
        vBaseTable,
        "tmpCommission",
            VAR vLoadPort = Port[Load Port]
            VAR vDischargePort = Port[Discharge Port]
            RETURN
                IF (
                    vLoadPort IN { "Port A", "Port B", "Port C" }
                        || vDischargePort IN { "Port A", "Port B", "Port C" },
                     ( [tmpFreight] + [tmpBunkerSurcharge] ) * .05,
                    0
                )
    )
VAR vResult =
    SUMX ( vCommissionTable, [tmpCommission] )
RETURN
    vResult

[dnsia]

Hi @DataInsights , 

Works fine for my data set! Thank you so much for your help!