Forum Discussion
Average Sales per Unit per Day
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:
- Per Unit Average Calculation
- Average per Unit = AVERAGEX( VALUES ('TABLE NAME'[Units), [Total Sales])
- Per Unit per Day Calculation
- Average per Unit per Day = AVERAGEX( VALUES ('TABLE NAME'[Days]), [Avg per Unit])
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
6 Replies
- tamerj1
Community Champion
Hi Chrisjab28
you may try
Average per Unit per day =
AVERAGEX (
SUMMARIZE ( 'TABLE NAME', 'TABLE NAME'[Units], 'TABLE NAME'[Days] ),
[Total Sales]
) Hello tamerj1 - Thank you for your reply. I did try this formula; however, it did not work.
- tamerj1
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] ) )
)
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
- v-yueyunzh-msft
Community Support
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
