Create Dynamic Summary Table Using Results of Measures

[WCrayger]

Hello,

I'm trying to create a dynamic summary table using the results of measure output. Essentially, I'm trying to create a historgram of sorts. I have 3 measures created, Median, Medan +5%, and Median -5%. I will also include Min and Max measure values once I figure this out. I have created a calculated table that does what I'm looking for, unfortunately, it is static. When I apply filters, the values in the calculated table are unchanged even though the columns are referencing variables that are equivalent to the desired measures. I'm wondering if this is possible, or if anyone has another idea of how to accomplish this. Please see the attached screenshots for detail, and the code below for my table. 

Thanks in advance! 

Test Table = 
var medianSubtotal = [Median ISP Subtotal]
var medianSubtotalMinus5 = [Median ISP Subtotal - 5%]
var medianSubtotalPlus5 = [Median ISP Subtotal + 5%]
return

UNION(
    SUMMARIZE(
        'ISP Invoice ISP Extended Subtotal Amount Calc Table'
        ,"Median Type", "Median ISP Extended Subtotal"
        ,"Subtotal Amount", medianSubtotal
    )
    ,SUMMARIZE(
        'ISP Invoice ISP Extended Subtotal Amount Calc Table'
        ,"Median Type", "Median ISP Extended Subtotal + 5%"
        ,"Subtotal Amount", medianSubtotalPlus5
    )
    ,SUMMARIZE(
        'ISP Invoice ISP Extended Subtotal Amount Calc Table'
        ,"Median Type", "Median ISP Extended Subtotal - 5%"
        ,"Subtotal Amount", medianSubtotalMinus5
    )
)

Desired final output, but dynamic in nature.Results of summary table with static values, working as expected.Page filter has been applied, output of summary table unaffected.

[shyammayhs]

This post really helped me out! Thanks! I have a question related to this - How do I order values in a particular order? Normally I can use the 'Sort by Column' feature but that doesnt work for this.

Thanks in advance!

@WCrayger @v-diye-msft 

[v-diye-msft · Accepted solution]

Hi @WCrayger ，

Calculated table is same as calculated column, the values can't be dynamically changed by filter. if you'd like to get the dynamic table, I'd suggest you add the measure instead of the column [Subtotal Amount ]:

Firstly, generate the below table:

Test Table = 
var medianSubtotal = [Median ISP Subtotal]
var medianSubtotalMinus5 = [Median ISP Subtotal - 5%]
var medianSubtotalPlus5 = [Median ISP Subtotal + 5%]
return

UNION(
    SUMMARIZE(
        'ISP Invoice ISP Extended Subtotal Amount Calc Table'
        ,"Median Type", "Median ISP Extended Subtotal"
    
    )
    ,SUMMARIZE(
        'ISP Invoice ISP Extended Subtotal Amount Calc Table'
        ,"Median Type", "Median ISP Extended Subtotal + 5%"
    
    )
    ,SUMMARIZE(
        'ISP Invoice ISP Extended Subtotal Amount Calc Table'
        ,"Median Type", "Median ISP Extended Subtotal - 5%"
   
    )
)

Then add the measure:

Subtotal Amount = SWITCH(MAX([Median Type]),"Median ISP Extended Subtotal",[Median ISP Subtotal],"Median ISP Extended Subtotal + 5%",[Median ISP Subtotal - 5%],"Median ISP Extended Subtotal - 5%",[Median ISP Subtotal - 5%])

[WCrayger]

@v-diye-msft, wonderful solution, and works perfectly. I'll be adding this one to my resource list. Thank you for your time, cheers.