Forum Discussion

orangeatom's avatar
orangeatom
Icon for Resolver I rankResolver I
8 years ago
Solved

SUM total for allselected values

Hi, I can't seem to get a good direction for this problem. My problem is calculating an aggregated SUM and showing the total per rows while filtering, example of matrix below: Site, Users, Total Use...
Need Help
  • orangeatom's avatar
    orangeatom
    8 years ago

    Thank you to alexei7Anonymous, Due to a bit of differences in my model the allselected would have worked however for my model the following resolved my issue:

    Calculate(

    SUM(LOOKUP[User]), 
    ALL(FACT[Country])

    )

Anonymous's avatar
Anonymous
Not applicable
8 years ago

I made a table with countries and a value for each contry. the following measure should solve your problem

 

Measure =
CALCULATE ( SUM ( Table1[Value] ), ALLSELECTED ( Table1[Country] ) )
  • orangeatom's avatar
    orangeatom
    Icon for Resolver I rankResolver I
    8 years ago

    Hello alexei7, Anonymous

     

    That seems to work when I look at my matrix and remove the row however when the row is included in the matrix, it shows the users per site:

     

    I think a better way to word my goal is to calculate the percent of total selected

     

    Current layout:

    Site, User, TotalUser, Contribution

    Canada, 10, 10, 100%, 

    America, 10, 10, 100%, 

    Brazil, 10, 10, 100%, 

     

    Intended layout:

    Site, User, TotalUser, Contribution

    Canada, 10, 30, 33%,

    America, 10, 30, 33%

    Brazil, 10, 30, 33%

     

    I'm not sure if this would also make a difference but my model is as follows:

    A fact table with performance (SiteFK) <-> Dimension table with countries (SiteSK, CountryFK) <-> Lookup table of country/users (CountrySK)

     

    • alexei7's avatar
      alexei7
      Icon for Continued Contributor rankContinued Contributor
      8 years ago

      Are you able to share a version of your model (obviously removing any sensitive data that you need to)?

       

      I don't really understand your model from what you've said.

       

      Thanks

      Alex

      • orangeatom's avatar
        orangeatom
        Icon for Resolver I rankResolver I
        8 years ago

        Hello alexei7, Yes certainly, i'm working on it and send a note.