Average Sales per Unit per Day

[Chrisjab28]

Have anyone found an effective way to calculate the Average per Units per Day in Power BI. 

I tried the following 2 steps approach unsuccessfully: 

1. Per Unit Average Calculation
   • Average per Unit = AVERAGEX( VALUES ('TABLE NAME'[Units), [Total Sales])
2. Per Unit per Day Calculation
   • Average per Unit per Day = AVERAGEX( VALUES ('TABLE NAME'[Days]), [Avg per Unit])

[v-yueyunzh-msft · Accepted solution]

Hi , @Chrisjab28 

According to your description, you calculate the average of different logics and look like a measure.
The calculation of the measure is directly related to the field you put above the visual. We cann see that you are using the  VALUES() function, which will only get the value of your current row. If you want to filter the entire table with the same Unit, you need to replace your function and use the FILTER() and ALLSELECTED() functions to find the value you need to average.

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Chrisjab28]

Thank you @tamerj1 for some reason the resulting average is not accurate. 

If I use the following formula:

Avg per Unit per day = ([Sales]/[Units]))/[Days], I am getting the right average per item; however the overall is still not accurate. 

[tamerj1]

@Chrisjab28 

Please try

= 
AVERAGEX ( 'TABLE NAME',  ( [Sales] / [Units] ) / [Days] )

[Chrisjab28]

Hello @tamerj1 - Thank you for your reply. I did try this formula; however, it did not work.

[tamerj1]

@Chrisjab28 

This is basically the same as youtr formula but using one measure 

Average per Unit per day =
AVERAGEX (
VALUES ( 'TABLE NAME'[Days] ),
CALCULATE ( AVERAGEX ( VALUES ( 'TABLE NAME'[Units] ), [Total Sales] ) )
)

[tamerj1]

Hi @Chrisjab28 

you may try 

Average per Unit per day =
AVERAGEX (
SUMMARIZE ( 'TABLE NAME', 'TABLE NAME'[Units], 'TABLE NAME'[Days] ),
[Total Sales]
)