Get an average from a measure by month

[patpois]

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

[patpois · Accepted solution]

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

[v-zhengdxu-msft]

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.

[patpois]

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:

@test = ('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