Forum Discussion
Calculate Average Based on Two Categories
I have two tables-- One with category types and one facts table that includes values. I want to calculate a column with the average of the values in the facts table based on two categories in the other table. What Dax expression would be best for this? The tables are connected by a many:many relationship using the "Library" column because the category types and the facts table have multiple instances of each library (for multiple years of data).
| Library | Library Visits Per Capita |
| Alpha | 43.01 |
| Beta | 27.11 |
| Gamma | 32.08 |
| Delta | 14.13 |
| Epsilon | 9.29 |
| Zeta | 50.39 |
| Eta | 22.42 |
| Theta | 12.13 |
| Iota | 14.03 |
| Kappa | 12.18 |
| Lambda | 47.04 |
| Mu | 14.4 |
| Nu | 39.23 |
| Pi | 2.11 |
| Rho | 47.27 |
| Sigma | 47.3 |
| Tau | 32.38 |
| Upsilon | 1.16 |
| Phi | 16.14 |
| Chi | 31.01 |
| Psi | 27.11 |
| Omega | 17.05 |
| Library | Population | Revenue |
| Alpha | 20,000 and Under | 10,000 and Under |
| Beta | 100,000 and Up | 10,000 and Under |
| Gamma | 20,000 and Under | 50,000 to 100,000 |
| Delta | 20,000 and Under | 50,000 to 100,000 |
| Epsilon | 100,000 and Up | 50,000 to 100,000 |
| Zeta | 20,000 and Under | 50,000 to 100,000 |
| Eta | 20,000 and Under | 100,000 and Up |
| Theta | 20,000 to 100,000 | 100,000 and Up |
| Iota | 20,000 to 100,000 | 100,000 and Up |
| Kappa | 20,000 to 100,000 | 100,000 and Up |
| Lambda | 20,000 and Under | 10,000 and Under |
| Mu | 20,000 and Under | 10,000 and Under |
| Nu | 20,000 to 100,000 | 10,000 and Under |
| Pi | 20,000 to 100,000 | 50,000 to 100,000 |
| Rho | 100,000 and Up | 50,000 to 100,000 |
| Sigma | 100,000 and Up | 100,000 and Up |
| Tau | 100,000 and Up | 100,000 and Up |
| Upsilon | 20,000 and Under | 10,000 and Under |
| Phi | 20,000 and Under | 50,000 to 100,000 |
| Chi | 20,000 to 100,000 | 100,000 and Up |
| Psi | 20,000 and Under | 50,000 to 100,000 |
| Omega | 20,000 and Under | 50,000 to 100,000 |
14 Replies
Hi 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?
Anonymous
you can create a column in second table as follows :
_visitpercapita =lookupvalue( 'library' [library visit per capita]) , 'library' [library] , 'your_table' [library])
and then create another column as follows:
avg = calculate (average(_visitpercapita) , filter ('your_table' , 'your_table' [population] = earlier ('your_table' [population])
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 tried this and received the following error message:
Filtered Visits Per Capita = lookupvalue(Facts[library visits per capita],Facts[Library], 'Filters'[Library])A single value for column 'Library' in table 'Filters' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
that is not correct, you should create this column in your fact table but use the library table in lookup. I mean this....
lookupvalue('Filters'[library visits per capita],'Filters'[Library], 'Fact'[Library])
