PowerBi How to calculate month sale deduct total average?

[Oscarr]

Hi all，

this Powerbi pic is per month average sale, how to calculate the latest month april deduct total average?

 

[Chihiro]

Depends on your model.

In general you will need to do one of the two.

1. Generate calculated table, summarizing sales by month. Then CALCULATE(Average(CalcTable[Sales]),ALL(CalcTable)). Then subtract from each month's sales

2. Pretty much same as above, but all done within single measure. Using variable to hold summarized table and calculate.

 

[Oscarr]

Depends on your model.

In general you will need to do one of the two.

1. Generate calculated table, summarizing sales by month. Then CALCULATE(Average(CalcTable[Sales]),ALL(CalcTable)). Then subtract from each month's sales

2. Pretty much same as above, but all done within single measure. Using variable to hold summarized table and calculate.

hi,

can you give me that powerbi sample file?

 

[Chihiro]

You should provide me with sample of how your data is structured (i.e. sample file). After all, I'm not a mind reader.

 

[Oscarr]

You should provide me with sample of how your data is structured (i.e. sample file). After all, I'm not a mind reader.

hi @Chihiro , this is the sample file...

that month i already get each month average sale, then i want this 4 month total average is how much?
example: jan 387,500.00 / Feb 488,888.89 / March 422,222.22 / april 437,500.00 Total Average =434,027.78 (i want this Total average 434,027.78 data)
and Total Average 434,027.78 - April 437,500.00 , Total = -3,472.22 (i want this Total = -3,472.22 data also)

Thanks

 

[Chihiro]

Sorry, but I don't use rar. If you can upload zip file I can take a look at it.

 

[Oscarr]

Sorry, but I don't use rar. If you can upload zip file I can take a look at it.

ok, here you go.

 

[Chihiro]

Personally, I don't like using Date Hierarchy that's auto generated by the engine.

At any rate... in this set up it's far easier to calculate average monthly by adding Month column to data. This can be done using DAX or in query stage.

Then Monthly average is calculated simply as...

MonthlyAvg = SUM(sale[Sale])/DISTINCTCOUNT(sale[Month])

Or using DIVIDE function. In this case, it shouldn't matter, as I can't imagine a case where this calculation will return DIV/0 error.

Then you can simply calculate the difference from given month's total.
Ex: Assuming you want the latest month's total.

CurrentMonthSales =
var cMonth = MAX(sale[Month])
Return
CALCULATE(SUM(sale[Sale]),FILTER(sale,sale[Month]=cMonth))

Then the final result would be...

Diff_CurMonth_Avg = [CurrentMonthSales]-[MonthlyAvg]

Note that you will need to change DAX measure, depending on what context you need to use it in.

 

[Oscarr]

Personally, I don't like using Date Hierarchy that's auto generated by the engine.

At any rate... in this set up it's far easier to calculate average monthly by adding Month column to data. This can be done using DAX or in query stage.

Then Monthly average is calculated simply as...

MonthlyAvg = SUM(sale[Sale])/DISTINCTCOUNT(sale[Month])

Or using DIVIDE function. In this case, it shouldn't matter, as I can't imagine a case where this calculation will return DIV/0 error.

Then you can simply calculate the difference from given month's total.
Ex: Assuming you want the latest month's total.

CurrentMonthSales =
var cMonth = MAX(sale[Month])
Return
CALCULATE(SUM(sale[Sale]),FILTER(sale,sale[Month]=cMonth))

Then the final result would be...

Diff_CurMonth_Avg = [CurrentMonthSales]-[MonthlyAvg]

Note that you will need to change DAX measure, depending on what context you need to use it in.

hi, i Try you give the code , cant make it

can you make code in that power bi , then send it back to me.

Thanks

 

[Chihiro]

Here.

 

[Oscarr]

Here.

Hi @Chihiro
i think the data wrong calculation.... i want the result is,

example: jan 387,500.00 / Feb 488,888.89 / March 422,222.22 / april 437,500.00 Total Average =434,027.78 (i want this Total average 434,027.78 data)

and Total Average 434,027.78 - April 437,500.00 , Total = -3,472.22 (i want this Total = -3,472.22 data also)

 

[Chihiro]

That's daily average not monthly avg. And in your initial sample you had Apr - Total. Should this be changed?
At any rate, concept is the same. You just have to change [Month] column used in calculation to [date] column.

 

[Oscarr]

That's daily average not monthly avg. And in your initial sample you had Apr - Total. Should this be changed?
At any rate, concept is the same. You just have to change [Month] column used in calculation to [date] column.

can you pls edit for me, then send back the power bi sample to me... thanks alot

 

[Chihiro]

You already have all the basis for applying the change in the previous file. I'd recommend you actually spend time to apply changes suggested to learn it yourself.

Having said that, I'll upload the file with changes applied one last time.

 

[Oscarr]

You already have all the basis for applying the change in the previous file. I'd recommend you actually spend time to apply changes suggested to learn it yourself.

Having said that, I'll upload the file with changes applied one last time.

Thanks for the help....