cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper II

Create new measure to get Average

Hi, I have a table which contains Date, CustomerName, Price, month, & weekday.

I have created measure using the DAX, Day 1 = CALCULATE(sum(Actual[Price]),FILTER('Actual','Actual'[Day]=1))

similarly for all days. If i select 3 months at a time i am getting the total values, as shown in the fig

My query is  I need to get an average value's if i select 2 or 3 months in the slicer.  Anyone has an idea how to solve this ?

1 ACCEPTED SOLUTION
Microsoft

Hi @siva3012,

Create an extra measure like:

`Count month selected = DISTINCTCOUNT(Actual[Month])`

Modify the measure for Day 1 as below:

`Day 1 = CALCULATE(sum(Actual[Price]),FILTER('Actual','Actual'[Day]=1))/[Count month selected]`

Result.

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Microsoft

Hi @siva3012,

Create an extra measure like:

`Count month selected = DISTINCTCOUNT(Actual[Month])`

Modify the measure for Day 1 as below:

`Day 1 = CALCULATE(sum(Actual[Price]),FILTER('Actual','Actual'[Day]=1))/[Count month selected]`

Result.

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper II

@v-yulgu-msft : Is it possible to draw an average line for those data which is shown in the graph ? Like this

Helper II

@v-yulgu-msft : Hi, There is an small eror in the solution. If we select 2 Months data Jan & Feb in slicer. Jan has 31 days and Feb has 28 days. The average is been calaculated wrongly for Jan 29, 30 & 31.

Do you have any idea over this ?

Helper II

I have solved this issue,

Day 1 = CALCULATE(sum(Actual[Price]),FILTER('Actual','Actual'[Day]=1))/(CALCULATE([Count month selected],Actual[Day]=1))

Helper II

@v-yulgu-msft : Thank you for the solution.

Announcements

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors