Forum Discussion
Calculation base on multiple criteria
Hi everyone, I need your help with a special case that I am working with.
I need to create a calculation , this is a multiplication base on multiple criteria.
1. I have a table called "tariffs" and this contain vendor number, service and tariff amount as below:
Vendor number service tariff Amount
2022 A 200
2023 B 300
2. I have a second table called "consolidation" whichs contain the details of the services, as below:
Number of services vendor service
2 2022 A
3 2023 B what I need is a measure called "Total Cost" to calculate the total of service that should be obtained multiplying "number of services" * "tariff amount" depending on the criteria selected.
Will be great if you can gime me your comments about this.
Thanks to all
4 Replies
// 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
Hi, walt20221
You can try the following methods.
Total Cost = SUM(tariffs[tariff Amount])*SUM(consolidation[Number of services])If that doesn't solve your problem, can you provide more sample data? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi:
You can put this calulated column in your Consolidation table, after you change data type of Vendor No. to "whole number",
Cost = LOOKUPVALUE(Tariffs[Tariff Amount], Tariffs[Vendor No.], Consolidation[Vendor],Tariffs[Service],Consolidation[Service])Then this measure for total amount:Total Amount = SUMX(Consolidation, Consolidation[No. Services] * Consolidation[Cost])I hope this helps.
