Forum Discussion

RohithGn's avatar
RohithGn
Icon for Helper I rankHelper I
7 years ago
Solved

Aggregated Sum

Hi,

 

I have a table siilar to below - 

 

SourceYearcountyschoolReponseTotal surveys
Hight School2018AsasdfHigh Positive20
Hight School2018AsasdfPositive20
Hight School2018AsasdfNegative20
Hight School2018AdgrewHigh Positive43
Hight School2018AdgrewPositive43
Hight School2018AdgrewNegative43
Elementary2018AdgrewHigh Positive43
Elementary2018AdgrewPositive43
Elementary2018AdgrewNegative43
Elementary2018AsasdfHigh Positive20
Elementary2018AsasdfPositive20
Elementary2018AsasdfNegative20

 

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

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    So, you will probably need a measure like:

     

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

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    7 years ago

    So, you will probably need a measure like:

     

    Measure = 
SUMX(SUMMARIZE('Table',[Source],[Year],[county],[school],"__total",AVERAGE([Total surveys])),[__total])
    • RohithGn's avatar
      RohithGn
      Icon for Helper I rankHelper I
      7 years ago

      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...