Forum Discussion

047's avatar
047
Frequent Visitor
4 years ago
Solved

Choose aggregation function dynamically

Hi

 

For measurement 'X', my table already has aggregated data which is aggregated using pandas before being imported into power BI. For example:

 

ProductCountryX_MeanX_90Percentile
ABCAustralia6500070000


There're mulitple visuals on the page all shaowing data based on X_mean, e.g. a filled map which colour codes the countries according to the values of X_mean. Is it possible to let the user choose the aggregation function (pre-calculated in this case), i.e. the user can choose to switch between report showing X_mean & report showing X_90th_percentile? Thanks

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi 047 

     

    The new preview feature Field parameters is probably what you want. Since your data is pre-calculated in different columns, you just need to select data from different columns when switching the choices. You can create separate measures like below to get values from single columns. Then create a field parameter and add these measures to the parameter. Use the field parameter in your visual to switch the aggregations. 

    Mean value = SELECTEDVALUE('Table'[X_Mean])
    90Percentile Value = SELECTEDVALUE('Table'[X_90Percentile])

    Other reference: How to dynamically switch between measures in Power BI visuals with Field Parameters 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

6 Replies

  • Pragati11's avatar
    Pragati11
    Super User
    4 years ago

    Hello 047 ,

     

    Are you looking for something like this?

    Here when SUM is selected, chart shows summation of values and so on.

     

  • 047's avatar
    047
    Frequent Visitor
    4 years ago

    Hi

    Thanks for the reply. I'm not sure if the example you showed has the data precalculated, so makes the calculation on the fly when selecting the Calc type in the slicer. Just to clarify, in my case, power BI doesn't have access to the raw data (with hundreds of thousands of rows). The data is already aggregated. Taking your example, the avg, median & sum of Sales by country are pre-calculated in different columns...something like this:

     

    CountrySales_avgSales_medianSales_Total
        
        

     

    Thanks

    • Pragati11's avatar
      Pragati11
      Super User
      4 years ago

      Hi 047 ,

       

      In my case I have a disconnected table for type of calculation and then I use this to create a dynamic measure which calculates dynamically the calculation and displays on the visual based on the selection in the slicer.

       

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    Hi 047 

     

    The new preview feature Field parameters is probably what you want. Since your data is pre-calculated in different columns, you just need to select data from different columns when switching the choices. You can create separate measures like below to get values from single columns. Then create a field parameter and add these measures to the parameter. Use the field parameter in your visual to switch the aggregations. 

    Mean value = SELECTEDVALUE('Table'[X_Mean])
    90Percentile Value = SELECTEDVALUE('Table'[X_90Percentile])

    Other reference: How to dynamically switch between measures in Power BI visuals with Field Parameters 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

    • 047's avatar
      047
      Frequent Visitor
      4 years ago

      Thanks for the answer. Reading about it, seems to do the job that I need. However, I'm using RS desktop version. I don't think this will be available anytime soon for the RS version since preview features are disabled.

      I will mark this as the answer to help anyone else looking for the answer though. Will look into this once it's available as a general feature. Thanks