cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RalstonT
Helper I
Helper I

Average Data

Hello,

 

Still farily new to Power BI, and really green with DAX.  Normally in Excel I would create a new row that shows the average of the information, but I am not sure how to do it in Power Bi.  Below are pictures of what I have in BI and an excel example that I use.  I am able to select the average of a Team in excel and compare it with 1 select individual.  In Power BI it shows the average line of all the associated, but when I select the associated it  will show just that person and the average of them.

Excel Example.PNGPower BI Example.PNG

12 REPLIES 12
Cmcmahan
Resident Rockstar
Resident Rockstar

Going back to your original question, since I don't think having the data will help.

For your chart, in the Values section, what value do you use to create the average line?  Is it a measure, or a value in the table?  You can set up a measure for that average line to ignore filters when you go down to the individual level.  Edit the following to fit your fields:

AveragePerformanceMeasure = CALCULATE(AVERAGE(Table1[Performance]), ALLEXCEPT(Table1, Table1[Person]))

This measure is getting the AVERAGE of the field, keeping all current filters except those on Person.  This way, when you select a single person, the average won't change. 

Hello,

 

I was able to get this working, but I think I need a solution that works a little differently.  I want to select a manager that has "x" amount of assocaites.  I want it to show that average of the entire team for the TM, and the average for the associate at once.  Not just selected.

Cmcmahan
Resident Rockstar
Resident Rockstar

All of this heavily depends on the context in which you're calculating this average. Is it only when you have a manager selected, or if no manager is selected, should it average all associates?  Once you've selected a manager, should the comparison average never change?

 

So just to be clear, you have performance data for associates.  Individuals are grouped under a manager.  You want to display each individual's aggregate data in a chart, as well as displaying the same aggregate data for the entire team in the same chart.  

Is this correct? What kind of visualization are you using?  Do you still want the average of the team as a comparison line, or should the team show up as another "person" in the list alongside the associates it is aggregating?

Hello,

 

Thanks for the reply.

 

I am using a line chart, and a bar chart for different points of data.  I would like the manager's team average to be shown when that manager is selected.  I would prefer to have the average of the team shows as another "person" so we can compare side by side.  In the excel example shown above that is what I created with the charts.  I had a line for the average of the team that was always constant.  Then I selected the average, and the associate in a slicer to compare.

Cmcmahan
Resident Rockstar
Resident Rockstar

Unfortunately, in PowerBI, there's not a good way to create a virtual row and display it in the default visuals alongside the non-aggregated data. You could create a second chart, hide the scale/title/legend/etc and put it into position next to the other table so it overlaps slightly and looks like another data cluster.

Hi,

I have thought about that, but to do that for every data set that I have would probably reduce the quality of the report.  Trying to see what DAX language would provide a selected result in the background, but again I am not up to snuff with it.

Thank you for your response.  I am trying to enter your suggestion, but I am not that familiar with DAX.  I am trying to find a course I can start to understand it.  What I get is "Too many arguements were passed to the AVERAGE function.  THe maximum argument count for the function is 1."

 

AveragePerformanceMeasure = CALCULATE(AVERAGE(AgentScorecard[AHT],[Average of Value average per Attribute],ALLEXCEPT(AgentScorecard,AgentScorecard[Associate])))
sokg
Solution Supplier
Solution Supplier

Can you provide us with sample data???

Hi,

 

Data.PNG

sokg
Solution Supplier
Solution Supplier

In a table form please , so we can use it with power bi.

We want to copy paste.

All your data is in one table???

Hello,

 

No I have quality metrics on another table, I have post call information on another, and then I have performance data on this one.  I have tables made up of associates, and one for managers as well.  I know it's not grat to have a flat table, but as I said I still pretty new at this.  Any tips I would gladly take, as of language I am pretty much self taught.

 

AssociatePeriodACW %Unattached ACW %Aux 9%Aux 9 MinutesAHTEMAIL APTNon-Compliant LunchNon-Compliant BreakTotal Non-CompliantCustomer XP AverageUtlization
Alborollosy, Hanaa138.18%5.08%0.26%24.201275.2225.43177210387 101.59%
Alborollosy, Hanaa234.64%2.45%0.46%51.021003.2221.3419920940870.76%100.32%
Alborollosy, Hanaa333.74%3.35%0.08%4.87793.7919.07519814955.77%100.00%
Allison, Jack10.20%1.18%1.03%102.33349.2813.5437549192.80%85.05%
Allison, Jack20.13%1.15%0.55%65.85386.1213.2621497088.38%89.19%
Allison, Jack30.04%0.94%1.08%104.03387.3113.162212394.19%89.72%
Allison, Jack40.03%2.04%1.07%103.05400.8814.0431285994.19%85.89%
Amey, Dianna17.48%3.40%2.27%217.17525.1512.6028434462897.22%0.00%
Amey, Dianna28.47%0.93%0.54%67.68510.689.7523656279857.13%0.00%
Amey, Dianna313.91%3.36%0.73%56.00580.629.307634642250.00%0.00%
Amey, Dianna47.15%3.35%0.57%45.78565.2514.4111034845893.94%0.00%

 

 

Anyone have any advice?

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors