Forum Discussion
Calculate Average Based on Two Categories
Solution SageOk, 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.
I used the above function and received this error:
The AVERAGE function only accepts a column reference as an argument.
- Selva-Salimi
Anonymous
as I said before, _visitpercapita is the name of column you calculated in the previous step.
in the image you shared the name is [filtered visit per capita], so replace it!