Forum Discussion

dnsia's avatar
dnsia
Icon for Helper II rankHelper II
5 years ago
Solved

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

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

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • DataInsights's avatar
    DataInsights
    5 years ago

    @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

    DataInsights_0-1605043136586.png

  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

4 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    5 years ago

    @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

    DataInsights_0-1605043136586.png

    • dnsia's avatar
      dnsia
      Icon for Helper II rankHelper II
      5 years ago

      Hi DataInsights , 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      dnsia
      Icon for Helper II rankHelper II
      5 years ago

      This works as well. Thank you!