Forum Discussion
Calculation base on multiple criteria
// I would first create a field
// in both tables to be able to
// join on it. Such a field could be
// created as a concatenation of
// Vendor number and Service, like
// 2022-A. The formula then would be
// faster and simpler. But if
// you insist on what you've shown then...
[Total Tariff] =
SUMX(
Consolidation,
var CurrentServiceAndVendor =
{ Consolidation[Service], Consolidation[Vendor] }
var NumOfServices = Consolidation[Number Of Services]
var Tariff_ =
CALCULATE(
SELECTEDVALUE( Tariffs[Amount] ) * NumOfServices,
TREATAS(
CurrentServiceAndVendor,
Tariffs[Service],
Tariffs[Vendor]
),
REMOVEFILTERS( Tariffs ) -- just in case
)
return
Tariff_
)
// If, on the other hand, you did what I've suggested, the
// measure would be:
[Total Tariff] =
SUMX(
Consolidation,
Consolidation[Number Of Services]
* RELATED( Tariffs[Amount] )
)
Thanks for your solution, but I think this is a little bit more complicated, maybe I was not consistent in my initial requirement. I would like to give you more details about my requirement, and I think this should evaluate multiple criteria, right now I just put here 2 vendors but I will have 8 vendros and the calculation could change for each vendor but the logic is the same as this.
------------------
We have purchase tariffs and another consolidation detail.
Consolidation detail:
Purchase tariffs
Calculation needed in Power BI:
Slot Costs:
Criteria for vendor “3262766” : from consolidation table get “container”, “zise” and “type”
If zise is “40” and type “RF” THEN Multiplicate total of containers “9” * Tariffs “3500” = 31500
If zise is “40” and type “HC” THEN Multiplicate total of containers “4” * Tariffs “3000” = 12000
If zise is “45” and type “DRY” THEN Multiplicate total of containers “1” * Tariffs “3550” = 3550
If column Haz in the table consolidation contains values, then multiply total of values * tariffs “250” in this case we have 6 containers are HAZ then 6* 250 = 1500
Special Costs:
If we have Special charges in the consolidation file, THEN multiplicate “Special Charges” *1 = 750
Bunker:
N/# We don’t have bunker for this vendor.
Operations details:
Total Slot Cost = 48550
Special Costs: 750
Bunker: 0
Total Cost = 49300
