Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I have a survey which can be completed by several people from the same company. I want the average score per category and this is done by taking the value in the score column and dividing it by the number of people who filled out the survey from the company.
However, the company participant count is a measure which has been calculated using the following:
I have the following table,
Company | Score | Category | Company Participant Count |
ABC | 129 | Cat1 | 3 |
ABC | 103 | Cat2 | 3 |
XYZ | 36 | Cat1 | 1 |
XYZ | 26 | Cat2 | 1 |
Solved! Go to Solution.
Here is a measure expression you can try
Avg for Company and Category =
CALCULATE (
AVERAGE ( Table[Score] ),
ALLEXCEPT (
Table,
Table[Company],
Table[Category]
)
)
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hey, so I used the measure you gave to recreate my static score column as a measure which I then divided by the measure for my participant count column and this has worked! I hope this was the intended use - Thank You!
If its not too much bother can you explain why the measure you provided calculates a row wise average for the score across company and category? I struggle a lot with PBI contexts at the minute 😕
Sure. The ALLEXCEPT function removes all the filters from the columns of the specified table except the the ones you list. The table visual provides filters on Category and Company because you added those columns to the visual, and the measure removes filters from any other columns you've included (e.g., Score, if not aggregated).
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Here is a measure expression you can try
Avg for Company and Category =
CALCULATE (
AVERAGE ( Table[Score] ),
ALLEXCEPT (
Table,
Table[Company],
Table[Category]
)
)
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |