Forum Discussion
Calculate Average Based on Two Categories
Solution SageHi Anonymous
in fact it is not that much clear what is your expectation, but based on my understanding you can write a measure as follows:
measure _avg := var _category = values (category_table [category])
return
calculate (average (value ) , filter ( fact, 'fact' [category] in _actegory))
if it doesn't work please share more details or some example about your expectation.
If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.
I want the average of the value by two categories. I have manually calculated what I am trying to do in this table:
| Library | Population | Revenue | Mean Visits Per Capita by Population and Revenue |
| Beta | 100,000 and Up | 10,000 and Under | 27.11 |
| Sigma | 100,000 and Up | 100,000 and Up | 39.84 |
| Tau | 100,000 and Up | 100,000 and Up | 39.84 |
| Epsilon | 100,000 and Up | 50,000 to 100,000 | 28.28 |
| Rho | 100,000 and Up | 50,000 to 100,000 | 28.28 |
| Alpha | 20,000 and Under | 10,000 and Under | 26.4025 |
| Lambda | 20,000 and Under | 10,000 and Under | 26.4025 |
| Mu | 20,000 and Under | 10,000 and Under | 26.4025 |
| Upsilon | 20,000 and Under | 10,000 and Under | 26.4025 |
| Eta | 20,000 and Under | 100,000 and Up | 22.42 |
| Gamma | 20,000 and Under | 50,000 to 100,000 | 26.15 |
| Delta | 20,000 and Under | 50,000 to 100,000 | 26.15 |
| Zeta | 20,000 and Under | 50,000 to 100,000 | 26.15 |
| Phi | 20,000 and Under | 50,000 to 100,000 | 26.15 |
| Psi | 20,000 and Under | 50,000 to 100,000 | 26.15 |
| Omega | 20,000 and Under | 50,000 to 100,000 | 26.15 |
| Nu | 20,000 to 100,000 | 10,000 and Under | 39.23 |
| Theta | 20,000 to 100,000 | 100,000 and Up | 17.3375 |
| Iota | 20,000 to 100,000 | 100,000 and Up | 17.3375 |
| Kappa | 20,000 to 100,000 | 100,000 and Up | 17.3375 |
| Chi | 20,000 to 100,000 | 100,000 and Up | 17.3375 |
| Pi | 20,000 to 100,000 | 50,000 to 100,000 | 2.11 |
In this table the "Mean visits per Capita by Population and Revenue" shows the average for libraries that have the same population category and the same population category. So Beta does not match other libraries in population or revenue so its average visits per capita is the same as the average visits per capita by population and revenue. However, Sigma and Tau have the same population and Revenue category--I want the average of their visits per capita based on the shared population and revenue. Therefore, the average by population and revenue for just those two libraries is
sum(library visits per capita)/count(libraries that meet the population and revenue requirements)
thus
[47.3 (#Sigma) + 32.38 (#Tau)]/2 (#the count of libraries) = 39.84
I want these values to calculate in a column in PowerBi--Is this possible?