cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Salman_85 Frequent Visitor
Frequent Visitor

Max, Median and Min

 

Hi,

This is summary table view from report. I need Max median and Min underneath total or may 3 different measure or any other solution and how to calculate these values. Please guide. Thanks.

 

requ.PNG

 

 

1 REPLY 1
Microsoft v-yulgu-msft
Microsoft

Re: Max, Median and Min

Hi @Salman_85,

 

Currently it is not supported to show max/min/median row like the total row under details data.

 

As a workaround, you can plot max/min/median values in column level.

1.PNG

 

Alternatively, to show max/min/median values in row level, you need a calendar table with a different table structure. 

 

In original table, create calculated column to generate total/max/min for each category.

Total for Revenue = SUM('summary table'[Revenue])
Max for Revenue = MAX('summary table'[Revenue])

3.PNG

Create calculated tables using below formulas:

New table1 =
UNION (
    SUMMARIZE (
        SELECTCOLUMNS (
            'summary table',
            "Executive", "Total",
            "Revenue", 'summary table'[Total for Revenue],
            "Bookings MRR", 'summary table'[Total for MRR]
        ),
        [Executive],
        "Revenue", AVERAGE ( 'summary table'[Total for Revenue] ),
        "bookings MRR", AVERAGE ( 'summary table'[Total for MRR] )
    ),
    SUMMARIZE (
        SELECTCOLUMNS (
            'summary table',
            "Executive", "Max",
            "Revenue", 'summary table'[Max for Revenue],
            "Bookings MRR", 'summary table'[Max for MRR]
        ),
        [Executive],
        "Revenue", AVERAGE ( 'summary table'[Max for Revenue] ),
        "bookings MRR", AVERAGE ( 'summary table'[Max for MRR] )
    )
)

4.PNG

 

New Table2 =
UNION (
    SELECTCOLUMNS (
        'summary table',
        "Executive", 'summary table'[Executive],
        "Revenue", 'summary table'[Revenue],
        "Bookings MRR", 'summary table'[Bookings MRR]
    ),
    'New table1'
)

5.PNG

 

Use a table visual to display data from 'New Table2'.

2.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors