Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

SUMIF - issues trying to recreate calculation

=IF(AX2="L",0,IF(AX2="J",AE2,SUMIF(AY:AY,AY2,AE:AE)))

 

I have this equation in excel and want to create a column in Power BI.

 

Column AX is a categorical column and describes the attribute which determines whether there should be a cost, AE is the cost of a job and AY is the Job number.

 

I have tried the calculation below and it is calculating correctly except for when Query1[M,LorJ]="M"

The number is far too high in this example because it seems to be adding the whole column. What I want is for it to sum the cost  of all jobs under that job number.

 

Ttl Cost = IF(Query1[M,L or J]="L",VALUE("0"),IF(Query1[M,L or J]="J",Query1[COST_NET_AP],IF(Query1[M,L or J]="M",(CALCULATE(SUM(Query1[COST_NET_AP]),Filter(Query1,Query1[M/L shipment#]=Query1[M/L shipment#]))))))

 

 

Thanks in advance.

 

Shem

Need Help

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    Hi Anonymous,

     

    If it is convenient, could you share some data sample and your desired output so that we could help further on it?

     

    Best  Regards,

    Cherry

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Cherry,

       

      Sorry i was finding it difficult to explain.

       

      The data is for a logistics company. Each of the rows in the below  data is a 'trip' or job. However there may be multiple jobs under one shipment number. The  'M' is the master trip, and 'L' is the leg of the trip. There can be multiple legs but only one master in a shipment. Only the Master has a price, but the legs have a cost and no price. What i want to do is remove the costs from the legs and roll them up to the Master trip so that the master trips has both the cost and the price.

       

      I hope this clarifies.

       

      Thanks