SUM total for allselected values

[orangeatom]

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

[Anonymous]

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]

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]

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]

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

[orangeatom · Accepted solution]

Thank you to @alexei7@Anonymous, 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])

)

[alexei7]

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