table showing min, median, and max

[scabral]

wondering if the following is possible to do in power bi:

i have a table with all of our clients with colunns for Client Reported Value and Combined Ratio.  I need to display the Min, Median, and Max for each column in the table as shown above.

thanks

Scott

[v-yangliu-msft · Accepted solution]

Hi  @scabral ,

I created some data:

Here are the steps you can follow：

1. Use Enter data to create a table.

2. Create measure.

Client_Max = MAXX(ALL('Table'),'Table'[Client reported value])

Client_Media = MEDIANX(ALL('Table'),'Table'[Client reported value])

Client_Min =
MINX(ALL('Table'),'Table'[Client reported value])

Com_Max =
MAXX(ALL('Table'),'Table'[Combined Ratio])

Com_Media =
MEDIANX(ALL('Table'),'Table'[Combined Ratio])

Com_Min =
MINX(ALL('Table'),'Table'[Combined Ratio])

Measure_Client report value =
SWITCH(
    TRUE(),
    MAX( Table2[Group])="Max",[Client_Max],
    MAX( Table2[Group])="Min",[Client_Min],
    MAX( Table2[Group])="Median",[Client_Media])

Measure_Combined Ratio =
SWITCH(
    TRUE(),
    MAX( Table2[Group])="Max",[Com_Max],
    MAX( Table2[Group])="Min",[Com_Min],
    MAX( Table2[Group])="Median",[Com_Media])

3. In Power query, Add Column – Index Column – From 1.

4. Select [Group] of Table2, click Column tools – Sort by – Index

5. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@scabral , This is measure to measure to dimension split. You need to have a table with all required filters of view by and use that in visual

union(
summarize('Table',"Measure","Client Report Value","Min",[Min Client Report Value],"Median",[MedianClient Report Value],"Max",[MaxClient Report Value]),
summarize('Table',"Measure","Combined Ratio","Min",[Min Combined Ratio],"Median",[MedianCombined Ratio],"Max",[Max Combined Ratio])
)

Assume already have these measures

Similar example

union(
summarize('Table',"Measure","Min","Test1",min('Table'[Test1]),"Test2",min('Table'[Test2]),"Test3",min('Table'[Test3]))
summarize('Table',"Measure","Max","Test1",max('Table'[Test1]),"Test2",max('Table'[Test2]),"Test3",max('Table'[Test3]))
)
"QTD" , "YTD" , "MTD"

union(
summarize('Table',"Measure","sales","This period",[SALES YTD],"Last period",[SALES LYTD],"POP",[SALES YOY])
summarize('Table',"Measure","unit","This period",[unit YTD],"Last period",[unit LYTD],"POP",[unit YOY])
)