Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Calculate sales over different rows

Hi all,

 

I've a question. In PBI i want to assign sales based on hours to the car_number. Problem is that i have only one row where the sales has been filled in, look below:

 

How can i solve this?

Regards, Cor

 

Need Help

6 Replies

  • Prateek97's avatar
    Prateek97
    Icon for Resolver III rankResolver III
    3 years ago

    Hi Anonymous ,

     

    Please try the following DAX.

     

    New Sales = 

    var og_sales = CALCULATE(SUM('Car Test'[Sales]),ALLEXCEPT('Car Test','Car Test'[OrderKey]))

    var total_hours = CALCULATE(SUM('Car Test'[Hours]),ALLEXCEPT('Car Test','Car Test'[OrderKey]))

    var new_sales = (og_sales/total_hours)*'Car Test'[Hours]

    return

    new_sales

     

     

    Please mark this as a solution. If it works for you.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Prateek97,

     

    Thanks for your answer, it works for almost all the transactions. But what code do i have to add to this code when the hours are zero and/or the sales are zero? Because i want when the hours are zero the original sales and when the sales are zero i want 0. Hope this is possible. 

    Thanks in advance. 

      • Prateek97's avatar
        Prateek97
        Icon for Resolver III rankResolver III
        3 years ago

        Hi Anonymous ,

         

        You just need to add a IF condition at  the end.

         

        New Sales = 

        var og_sales = CALCULATE(SUM('Car Test'[Sales]),ALLEXCEPT('Car Test','Car Test'[OrderKey]))

        var total_hours = CALCULATE(SUM('Car Test'[Hours]),ALLEXCEPT('Car Test','Car Test'[OrderKey]))

        var new_sales = (og_sales/total_hours)*'Car Test'[Hours]

        return

        IF(og_sales=0,0,IF(total_hours=0,og_sales,new_sales))
         
        Let me know if it works.