Calculation base on multiple criteria

[walt20221]

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

[Whitewater100]

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. 

[v-zhangti]

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.

[daXtreme]

// 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] )
)

[walt20221]

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