Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago

Single cost calculation for multiple orders

Hello

I have a table with orders in different statuses and I need to calculate the cost only once for each order but I don't get it right.

When I put this expression at the column level, it works fine

CALCULATE(SUM(Price[Price])*sum(Touched[may-24])*sum(Agencies[Department])*SUM(AHT[may-24]), Agencies[Agency]="Marktel",Price[Agency] = "Marktel")
But when I try to put in the measurement it doesn't work anymore and I can't leave it at the column level because then in the view it adds the cost of each line and in total it comes out badly.
SUMX(DISTINCT(Sales[OT No.]),CALCULATE(SUM(Price[Price])*sum(Touched[may-24])*sum(Agencies[Department])*SUM(AHT[may-24]), Agencies[Agency]="Marktel",Price[Agency] = "Marktel"))
Can anyone help me?
Best regards,
Thais

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Syndicate_Admin ,

    In order to better understand your demands and give the right solution, could you please provide some more specific information? such as your desensitized example data and a screenshot of your desired results?

     

    Thanks for your efforts & time in advance.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    2 years ago

    Hello

    This is the table, as you can see the cost column is where the formula is:

    Cost = CALCULATE(SUM(Price[Price])*sum(Touched[may-24])*sum(Agencies[Department])*SUM(AHT[may-24]), Agencies[Agency]="Marktel",Price[Agency] = "Marktel")+
    CALCULATE(SUM(Price[Price])*sum(Touched[may-24])*sum(Agencies[Department])*SUM(AHT[may-24]), Agencies[Agency]="Bosch",Price[Agency] = "Bosch")

    And it does the calculation well, but when I take the model it brings me 2 times the cost for the same order, so I have tried to make this measurement:

    Provi Ribs = sumx(Sales,CALCULATE(SUM(Price[Price])*sum(Touched[may-24])*sum(Agencies[Department])*SUM(AHT[may-24]), Agencies[Agency]="Marktel",Price[Agency] = "Marktel", DISTINCT(Sales[OT No.])))+sumx(Sales,CALCULATE(SUM(Price[Price])*sum(Touched[may-24])*sum(Agencies[Department])*SUM(AHT[may-24]), Agencies[Agency]="Bosch",Price[Agency] = "Bosch", DISTINCT(Sales[OT No.])))
    But it's still doubling the cost.