topic Re: Average Sales per Unit per Day in DAX Commands and Tips

Average Sales per Unit per Day

Chrisjab28 — Mon, 12 Dec 2022 17:00:04 GMT

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])

Re: Average Sales per Unit per Day

tamerj1 — Mon, 12 Dec 2022 17:22:17 GMT

Hi Chrisjab28

you may try

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

Re: Average Sales per Unit per Day

Chrisjab28 — Mon, 12 Dec 2022 17:52:54 GMT

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

Re: Average Sales per Unit per Day

tamerj1 — Mon, 12 Dec 2022 18:04:35 GMT

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] ) )
)

Re: Average Sales per Unit per Day

Chrisjab28 — Mon, 12 Dec 2022 19:57:06 GMT

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.

Re: Average Sales per Unit per Day

tamerj1 — Tue, 13 Dec 2022 10:09:34 GMT

Chrisjab28

Please try

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

Re: Average Sales per Unit per Day

v-yueyunzh-msft — Tue, 13 Dec 2022 02:39:39 GMT

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