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

Reply
patpois
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.

 

patpois_2-1716322873486.png

 

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:

patpois_1-1716322391929.png

 

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
patpois
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.

patpois_1-1716561028666.png

 

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

patpois_2-1716561068995.png

 

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:

patpois_0-1716560784638.png

 

So, hope this will help other people.

Have i nice day

 

 

View solution in original post

3 REPLIES 3
patpois
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.

patpois_1-1716561028666.png

 

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

patpois_2-1716561068995.png

 

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:

patpois_0-1716560784638.png

 

So, hope this will help other people.

Have i nice day

 

 

v-zhengdxu-msft
Community Support
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.

 

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.

patpois_0-1716398205652.png

 

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.

patpois_1-1716401608673.png

 

patpois_2-1716401733594.png

 

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

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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