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.
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.
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.
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?
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.
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.
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."
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.
|Associate||Period||ACW %||Unattached ACW %||Aux 9%||Aux 9 Minutes||AHT||EMAIL APT||Non-Compliant Lunch||Non-Compliant Break||Total Non-Compliant||Customer XP Average||Utlization|
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.