Forum Discussion

WCrayger's avatar
WCrayger
Frequent Visitor
7 years ago
Solved

Create Dynamic Summary Table Using Results of Measures

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.

  • 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%])

     

     

3 Replies

  • v-diye-msft's avatar
    v-diye-msft
    Community Support

    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's avatar
      WCrayger
      Frequent Visitor

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

  • 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