Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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 ot...
Need Help
Selva-Salimi's avatar
Selva-Salimi
Solution Sage
1 year ago

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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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?

LibraryPopulationRevenuePopulation and RevenueMean Visits Per Capita by Population and Revenue
Beta100,000 and Up10,000 and Under100,000 and Up & 10,000 and Under27.11
Sigma100,000 and Up100,000 and Up100,000 and Up & 100,000 and Up39.84
Tau100,000 and Up100,000 and Up100,000 and Up & 100,000 and Up39.84
Epsilon100,000 and Up50,000 to 100,000100,000 and Up & 50,000 to 100,00028.28
Rho100,000 and Up50,000 to 100,000100,000 and Up & 50,000 to 100,00028.28
Alpha20,000 and Under10,000 and Under20,000 and Under & 10,000 and Under26.4025
Lambda20,000 and Under10,000 and Under20,000 and Under & 10,000 and Under26.4025
Mu20,000 and Under10,000 and Under20,000 and Under & 10,000 and Under26.4025
Upsilon20,000 and Under10,000 and Under20,000 and Under & 10,000 and Under26.4025
Eta20,000 and Under100,000 and Up20,000 and Under & 100,000 and Up22.42
Gamma20,000 and Under50,000 to 100,00020,000 and Under & 50,000 to 100,00026.15
Delta20,000 and Under50,000 to 100,00020,000 and Under & 50,000 to 100,00026.15
Zeta20,000 and Under50,000 to 100,00020,000 and Under & 50,000 to 100,00026.15
Phi20,000 and Under50,000 to 100,00020,000 and Under & 50,000 to 100,00026.15
Psi20,000 and Under50,000 to 100,00020,000 and Under & 50,000 to 100,00026.15
Omega20,000 and Under50,000 to 100,00020,000 and Under & 50,000 to 100,00026.15
Nu20,000 to 100,00010,000 and Under20,000 to 100,000 & 10,000 and Under39.23
Theta20,000 to 100,000100,000 and Up20,000 to 100,000 & 100,000 and Up17.3375
Iota20,000 to 100,000100,000 and Up20,000 to 100,000 & 100,000 and Up17.3375
Kappa20,000 to 100,000100,000 and Up20,000 to 100,000 & 100,000 and Up17.3375
Chi20,000 to 100,000100,000 and Up20,000 to 100,000 & 100,000 and Up17.3375
Pi20,000 to 100,00050,000 to 100,00020,000 to 100,000 & 50,000 to 100,0002.11
  • Selva-Salimi's avatar
    Selva-Salimi
    Solution Sage
    1 year ago

    so why did you need library table?! all data seems to be in fact table. am I right?!

     

    isn't it your library table?..

    LibraryLibrary Visits Per Capita
    Alpha43.01
    Beta27.11

     

    where the average should be calculated based on these values in this table?

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      I have a facts table and a filters table:

       

      Filters Table--

      LibraryPopulationRevenuePopulation and Revenue
      Beta100,000 and Up10,000 and Under100,000 and Up & 10,000 and Under
      Sigma100,000 and Up100,000 and Up100,000 and Up & 100,000 and Up
      Tau100,000 and Up100,000 and Up100,000 and Up & 100,000 and Up
      Epsilon100,000 and Up50,000 to 100,000100,000 and Up & 50,000 to 100,000
      Rho100,000 and Up50,000 to 100,000100,000 and Up & 50,000 to 100,000
      Alpha20,000 and Under10,000 and Under20,000 and Under & 10,000 and Under
      Lambda20,000 and Under10,000 and Under20,000 and Under & 10,000 and Under
      Mu20,000 and Under10,000 and Under20,000 and Under & 10,000 and Under
      Upsilon20,000 and Under10,000 and Under20,000 and Under & 10,000 and Under
      Eta20,000 and Under100,000 and Up20,000 and Under & 100,000 and Up
      Gamma20,000 and Under50,000 to 100,00020,000 and Under & 50,000 to 100,000
      Delta20,000 and Under50,000 to 100,00020,000 and Under & 50,000 to 100,000
      Zeta20,000 and Under50,000 to 100,00020,000 and Under & 50,000 to 100,000
      Phi20,000 and Under50,000 to 100,00020,000 and Under & 50,000 to 100,000
      Psi20,000 and Under50,000 to 100,00020,000 and Under & 50,000 to 100,000
      Omega20,000 and Under50,000 to 100,00020,000 and Under & 50,000 to 100,000
      Nu20,000 to 100,00010,000 and Under20,000 to 100,000 & 10,000 and Under
      Theta20,000 to 100,000100,000 and Up20,000 to 100,000 & 100,000 and Up
      Iota20,000 to 100,000100,000 and Up20,000 to 100,000 & 100,000 and Up
      Kappa20,000 to 100,000100,000 and Up20,000 to 100,000 & 100,000 and Up
      Chi20,000 to 100,000100,000 and Up20,000 to 100,000 & 100,000 and Up
      Pi20,000 to 100,00050,000 to 100,00020,000 to 100,000 & 50,000 to 100,000

       

      Facts Table:

      LibraryLibrary Visits Per Capita
      Alpha43.01
      Beta27.11
      Gamma32.08
      Delta14.13
      Epsilon9.29
      Zeta50.39
      Eta22.42
      Theta12.13
      Iota14.03
      Kappa12.18
      Lambda47.04
      Mu14.4
      Nu39.23
      Pi2.11
      Rho47.27
      Sigma47.3
      Tau32.38
      Upsilon1.16
      Phi16.14
      Chi31.01
      Psi27.11
      Omega17.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's avatar
        Selva-Salimi
        Solution Sage
        1 year ago

        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