Aggregated Sum

[RohithGn]

Hi,

I have a table siilar to below - 

Source	Year	county	school	Reponse	Total surveys
Hight School	2018	As	asdf	High Positive	20
Hight School	2018	As	asdf	Positive	20
Hight School	2018	As	asdf	Negative	20
Hight School	2018	Ad	grew	High Positive	43
Hight School	2018	Ad	grew	Positive	43
Hight School	2018	Ad	grew	Negative	43
Elementary	2018	Ad	grew	High Positive	43
Elementary	2018	Ad	grew	Positive	43
Elementary	2018	Ad	grew	Negative	43
Elementary	2018	As	asdf	High Positive	20
Elementary	2018	As	asdf	Positive	20
Elementary	2018	As	asdf	Negative	20

When I am trying to sum the Total Surveys, it is summing up all the numbers in Total surveys column, I have slicers in my report for Source, year, School name, Response based on slicer selection, the sum should consider one total survey as total surveys are repetitive.

Any pointers are highly appreciated

Regards,

Rohith

[Greg_Deckler · Accepted solution]

So, you will probably need a measure like:

Measure = 
SUMX(SUMMARIZE('Table',[Source],[Year],[county],[school],"__total",AVERAGE([Total surveys])),[__total])

[Greg_Deckler · Accepted solution]

So, you will probably need a measure like:

Measure = 
SUMX(SUMMARIZE('Table',[Source],[Year],[county],[school],"__total",AVERAGE([Total surveys])),[__total])

[RohithGn]

Thanks for the reply Greg.

Here is my expected output:

By default the sum should be 20+43+20+43=126

If i select Highschool in my Source slicer, the result should be 20+43 =63

If i select Highschool and the County as AS, the result should be 20

If i select County as AS, the result should be 20(High School)+20(Elementary) = 40

and so on...

[v-chuncz-msft · Accepted solution]

@RohithGn,

The measure above seems to work. Here is a similar post for your reference.

[PattemManohar]

@RohithGn Please post your expected output as well.