Calculate sales over different rows

[Anonymous]

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

[Anonymous]

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. 

[Anonymous]

Anyone an idea @Prateek97 ?

[Prateek97]

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.

[Anonymous]

Thanks. The problem is for 99% solved. I've test it and sometime it get some weird numbers: 

How can i solve this @Prateek97?

[Prateek97]

HI @Anonymous ,

The answer seems correct.

Total Sales = 616.24

Total Hours = 8.25

Sales per Hour = 74.695

Row1 = 74.695 * 2 = 149.3915

Row2 = 74.695* 6.25 = 466.8485

Please let me know where is the error

[Prateek97]

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.