Forum Discussion
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 Users
Canada, 10
America, 10
Brasil, 10
France, 10
I would like to have the total users column show the total for the selected values, filter by region "Americas" you should get:
Site, Users, Total Users
Canada, 10, 30
America, 10, 30
Brasil, 10, 30
hidden is France, 10 but if the user was to unselect the region filter the table would show
Canada, 10, 40
America, 10, 40
Brasil, 10, 40
France, 10, 40
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]))
6 Replies
Hi orangeatom,
Try the following and see if it helps (replacing "Table1") with your own table name:
Total Users = CALCULATE(sum(Table1[Users]),ALLSELECTED(Table1[Site]))
Alex
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)
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
