Forum Discussion

SebKaliVith's avatar
SebKaliVith
Regular Visitor
8 years ago
Solved

Summarize table

Hi all,

 

I need your help for an easy trick :

 

I have this table :

 

 

Table1

Hospital      Step      Progress

A                 Step1      50%

A                 S tep2      30%

A                 Step3      10%

B                 Step1      10%

B                 Step2       8%

B                 Step3       6%

C                 Step1       1%
C                 Step2       0%

C                Step3        0%

 

I would like to obtain this table :

 

Table 2

Hospital       Average

A                     30%

B                      8%

C                    0.33% 

 

How can i go from table 1 to table 2 ?

 

Thanks a lot !

 

Regards,

Séb

Need Help
  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    Hi SebKaliVith,

     

    You could try this formula below.

     

    Table 2 =
SUMMARIZE (
    'Table',
    'Table'[Hospital],
    "average", CALCULATE (
        AVERAGE ( 'Table'[Progress] ),
        FILTER ( 'Table', 'Table'[Hospital] = MAX ( 'Table'[Hospital] ) )
    )
)

    Here is the output you expected.

     

     

    Best  Regards,

    Cherry

4 Replies

  • jthomson's avatar
    jthomson
    Solution Sage
    8 years ago

    Just make a measure that calculates the average of the progress column, then drop that and hospital into a table

    • SebKaliVith's avatar
      SebKaliVith
      Regular Visitor
      8 years ago

      Hello,

       

      Thank you for your answer !

      But i obtain this table :

       

      TABLE 2

      Hospital     AVerage

      A                    19%

      B                     19%

      C                     19%

       

      I obtain the average of all my hospital progress, but i would like the average for each hospital.

       

       

      Any idea ?

       

      Best,

      Séb

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    8 years ago

    Hi SebKaliVith,

     

    You could try this formula below.

     

    Table 2 =
SUMMARIZE (
    'Table',
    'Table'[Hospital],
    "average", CALCULATE (
        AVERAGE ( 'Table'[Progress] ),
        FILTER ( 'Table', 'Table'[Hospital] = MAX ( 'Table'[Hospital] ) )
    )
)

    Here is the output you expected.

     

     

    Best  Regards,

    Cherry