Forum Discussion
Calculate Average Based on Two Categories
Solution Sageso 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?
I have a facts table and a filters table:
Filters Table--
| Library | Population | Revenue | Population and Revenue |
| Beta | 100,000 and Up | 10,000 and Under | 100,000 and Up & 10,000 and Under |
| Sigma | 100,000 and Up | 100,000 and Up | 100,000 and Up & 100,000 and Up |
| Tau | 100,000 and Up | 100,000 and Up | 100,000 and Up & 100,000 and Up |
| Epsilon | 100,000 and Up | 50,000 to 100,000 | 100,000 and Up & 50,000 to 100,000 |
| Rho | 100,000 and Up | 50,000 to 100,000 | 100,000 and Up & 50,000 to 100,000 |
| Alpha | 20,000 and Under | 10,000 and Under | 20,000 and Under & 10,000 and Under |
| Lambda | 20,000 and Under | 10,000 and Under | 20,000 and Under & 10,000 and Under |
| Mu | 20,000 and Under | 10,000 and Under | 20,000 and Under & 10,000 and Under |
| Upsilon | 20,000 and Under | 10,000 and Under | 20,000 and Under & 10,000 and Under |
| Eta | 20,000 and Under | 100,000 and Up | 20,000 and Under & 100,000 and Up |
| Gamma | 20,000 and Under | 50,000 to 100,000 | 20,000 and Under & 50,000 to 100,000 |
| Delta | 20,000 and Under | 50,000 to 100,000 | 20,000 and Under & 50,000 to 100,000 |
| Zeta | 20,000 and Under | 50,000 to 100,000 | 20,000 and Under & 50,000 to 100,000 |
| Phi | 20,000 and Under | 50,000 to 100,000 | 20,000 and Under & 50,000 to 100,000 |
| Psi | 20,000 and Under | 50,000 to 100,000 | 20,000 and Under & 50,000 to 100,000 |
| Omega | 20,000 and Under | 50,000 to 100,000 | 20,000 and Under & 50,000 to 100,000 |
| Nu | 20,000 to 100,000 | 10,000 and Under | 20,000 to 100,000 & 10,000 and Under |
| Theta | 20,000 to 100,000 | 100,000 and Up | 20,000 to 100,000 & 100,000 and Up |
| Iota | 20,000 to 100,000 | 100,000 and Up | 20,000 to 100,000 & 100,000 and Up |
| Kappa | 20,000 to 100,000 | 100,000 and Up | 20,000 to 100,000 & 100,000 and Up |
| Chi | 20,000 to 100,000 | 100,000 and Up | 20,000 to 100,000 & 100,000 and Up |
| Pi | 20,000 to 100,000 | 50,000 to 100,000 | 20,000 to 100,000 & 50,000 to 100,000 |
Facts Table:
| 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 |
I want to find a formula/calculation that will automatically calculate the Mean Visits Per Capita by Population and Revenue values that I provided above.
- Selva-Salimi
Ok, based on your first description the fact table repeated per year so first of all write this and let me know if it works...
Filtered Visits Per Capita = lookupvalue(Facts[library visits per capita],Facts[Library], 'Filters'[Library] , Facts[year] , filters[year]) --add any other column that you think need to make it unique
This does not seem to be working--it just returns the value that is listed in the facts table. Unless you are planning on using this new column with another formula?
Also--I was not able to use the formula in the facts table, only in the filters table.
- Selva-Salimi
As I said before, it was the first step. the second step is to write another column as follows:
avg = calculate (average(_visitpercapita) , filter ('filter' , 'filter' [population] = earlier ('filer' [population] && 'filters' [Revenue]= earlier ('filters' [revenue])))
** _visitpercapita is the name of column you calculated in the previous step **
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.