Forum Discussion
Calculate Average Based on Two Categories
Solution SageAnonymous
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:
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.
- Selva-Salimi
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])
Okay-- the "library visits per capita" values are in the facts table, not the filters table. That formula did not work.
I have tried something different, I used a formula to concatenate the population and revenue brackets so that now there is only one dimension I would need to focus on:
Would this make the calculation easier?
Library Population Revenue Population and Revenue Mean Visits Per Capita by Population and Revenue Beta 100,000 and Up 10,000 and Under 100,000 and Up & 10,000 and Under 27.11 Sigma 100,000 and Up 100,000 and Up 100,000 and Up & 100,000 and Up 39.84 Tau 100,000 and Up 100,000 and Up 100,000 and Up & 100,000 and Up 39.84 Epsilon 100,000 and Up 50,000 to 100,000 100,000 and Up & 50,000 to 100,000 28.28 Rho 100,000 and Up 50,000 to 100,000 100,000 and Up & 50,000 to 100,000 28.28 Alpha 20,000 and Under 10,000 and Under 20,000 and Under & 10,000 and Under 26.4025 Lambda 20,000 and Under 10,000 and Under 20,000 and Under & 10,000 and Under 26.4025 Mu 20,000 and Under 10,000 and Under 20,000 and Under & 10,000 and Under 26.4025 Upsilon 20,000 and Under 10,000 and Under 20,000 and Under & 10,000 and Under 26.4025 Eta 20,000 and Under 100,000 and Up 20,000 and Under & 100,000 and Up 22.42 Gamma 20,000 and Under 50,000 to 100,000 20,000 and Under & 50,000 to 100,000 26.15 Delta 20,000 and Under 50,000 to 100,000 20,000 and Under & 50,000 to 100,000 26.15 Zeta 20,000 and Under 50,000 to 100,000 20,000 and Under & 50,000 to 100,000 26.15 Phi 20,000 and Under 50,000 to 100,000 20,000 and Under & 50,000 to 100,000 26.15 Psi 20,000 and Under 50,000 to 100,000 20,000 and Under & 50,000 to 100,000 26.15 Omega 20,000 and Under 50,000 to 100,000 20,000 and Under & 50,000 to 100,000 26.15 Nu 20,000 to 100,000 10,000 and Under 20,000 to 100,000 & 10,000 and Under 39.23 Theta 20,000 to 100,000 100,000 and Up 20,000 to 100,000 & 100,000 and Up 17.3375 Iota 20,000 to 100,000 100,000 and Up 20,000 to 100,000 & 100,000 and Up 17.3375 Kappa 20,000 to 100,000 100,000 and Up 20,000 to 100,000 & 100,000 and Up 17.3375 Chi 20,000 to 100,000 100,000 and Up 20,000 to 100,000 & 100,000 and Up 17.3375 Pi 20,000 to 100,000 50,000 to 100,000 20,000 to 100,000 & 50,000 to 100,000 2.11 - Selva-Salimi
so why did you need library table?! all data seems to be in fact table. am I right?!
isn't it your library table?..
Library Library Visits Per Capita Alpha 43.01 Beta 27.11 where the average should be calculated based on these values in this table?