Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
Proud to be a Super User!
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.
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.
This works as well. Thank you!
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
Proud to be a Super User!
Hi @DataInsights ,
Works fine for my data set! Thank you so much for your help!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |