Calculate Average Based on Two Categories

[Sawyer_House]

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

[Anonymous]

Hi @Sawyer_House 

Has your problem been resolved? If so, could you mark the corresponding reply as the solution so that others with similar issues can benefit from it?

Best Regards,

Jayleny

[Selva-Salimi]

@Sawyer_House 

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. 

[Sawyer_House]

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.

[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])

[Sawyer_House]

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?

[Sawyer_House]

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 

[Sawyer_House]

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. 

[Sawyer_House]

I used the above function and received this error:

The AVERAGE function only accepts a column reference as an argument.

[Selva-Salimi]

@Sawyer_House 

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! 

[Selva-Salimi]

Hi @Sawyer_House

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. 

[Sawyer_House]

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?