Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Get an average from a measure by month

Hi

I have created a measure to get employees productivity.

This measure is presented in a clustered column chart.

From the clustered column chart, i then add a Date table and the measure. I understand that without the Date table, it will display only one value, the total calculated by Power Bi.

So the measure is displayed by Month in the chart.

That part is working fine.

I'm trying to display in a pie chart, that will average all values from this measure.

The problem is that it will display only the total calculated by Power BI.

Example from a table chart:

The pie chart display 35.35 and the average should be 21.06.

So, i'm struggling to get the measure to take each value by month and then make an average.

My question is:

How can i tell power bi to take all values by month and average it ?

Thank you

1 ACCEPTED SOLUTION
Regular Visitor

Hi

I think, i found a solution to get the average for a measure.

Here's the step i did:

I selected the pie chart where my @test was and on the visualizations panel, I selected the @test value. Then selected the option New quick measure.

In the quick measure panel, I selected Average per category, base value = @test & Category = Date table per Month name.

The result is =

@test average per MonthName =
AVERAGEX(
KEEPFILTERS(VALUES('Date'[MonthName])),
CALCULATE(@test)
)

This new measure gave me the right result for this employee = 21.06

I then create a new measure the same way for each employee & all have the right average value.

As show in the pie chart below:

So, hope this will help other people.

Have i nice day

3 REPLIES 3
Regular Visitor

Hi

I think, i found a solution to get the average for a measure.

Here's the step i did:

I selected the pie chart where my @test was and on the visualizations panel, I selected the @test value. Then selected the option New quick measure.

In the quick measure panel, I selected Average per category, base value = @test & Category = Date table per Month name.

The result is =

@test average per MonthName =
AVERAGEX(
KEEPFILTERS(VALUES('Date'[MonthName])),
CALCULATE(@test)
)

This new measure gave me the right result for this employee = 21.06

I then create a new measure the same way for each employee & all have the right average value.

As show in the pie chart below:

So, hope this will help other people.

Have i nice day

Community Support

Hi @patpois

Could you please share more information, such as what is the dax code you're using or share some data(exclude sensitive data) so that we can help you better.

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards

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

Regular Visitor

Hi Zhengdong Xu

Here's more details.

From differents sources, I created some measures to be able to have a way to compare employees on productivity. So, i took information from timesheet & compare it to the work done. From that i'm able to compare employees.

I have a global measures & a measures by department. An employees can be part of one or more departments. So each employee has at least two measures for him (one by department + one global)

With the global measure, i'm able to produce a clustered column chart by month as show below.

That part is working fine.

When i tried to use the same measure for a pie chart (could be another type of chart), it will display the total value of 35.35 and i don't have the option in the chart to make it an average.

I want to have a simple average result base on the 4 values shown above 18.12, 7.84, 29.70 & 28.57 and divide it by the number of Month, in that case 4.  Result should be 21.06.

I created a measure for each employees by department. An employee can be part of one or more department. It is base on the timesheet of the employee and the work done in that department. From that a new mesure was created for this employee to sum the measure for an employee part of 2 or more departments.

The department measure is:

TEST B Employee Productivity Global = IF('Measure Availability'[Global Employee Avail/Month]=0,'Measure B'[B GlobalWks Employee] / 'Measure B'[B Employee Hour/Month],'Measure B'[B GlobalWks Employee] / 'Measure B'[B Employee Hour/Month] - ('Measure B'[B GlobalWks Employee] / 'Measure B'[B Employee Hour/Month] * ('Measure Availability'[Global Employee Avail/Month] / (Constant[regular week Hrs] * Constant[Week per Month] * Constant[NB Month])))) * 100

The global measure that sum the 2 departments for this employee:

('Measure B'[TEST B Employee Productivity Global] + 'Measure A'[TEST A Employee Productivity Global])

Glossary:
Constant[regular week Hrs] = 40
Constant[Week per Month] = 4.34524
Constant[NB Month] = DATEDIFF(DATE(2024,1,1),TODAY(),MONTH)+1
Measure B'[B GlobalWks Employee] = The sum of all the tickets done by an employees for department B. Example = 160
Measure B'[B Employee Hour/Month] = The number of hours for the Month done by the employee. Example = 800 hrs
Measure Availability'[Global Employee Avail/Month] = The number of hours for the Month were the employee didn't have work to be done. Example = 100 hrs

So for the department measure base on the example giving the result should be 17.70 for 5 Month.

From the @test measure, i want to find a way to get an average of all the values per Month that was shown above. I know, that i need to adjust or put the measure in a virtual table but i'm struggling to find the right way to do this. It's pretty simple but i'm still learning Power BI and i'm stuck there !

Hope it's possible to do it and i'm open to suggestion here.

Have i nice day

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors