Choose aggregation function dynamically

[047]

Hi

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

Product	Country	X_Mean	X_90Percentile
ABC	Australia	65000	70000

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

[v-jingzhang · Accepted solution]

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]

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

[v-jingzhang]

Hi @047 

You can try the solution in this blog: Change the Column or Measure Value in a Power BI Visual by Selection of the Slicer: Parameter Table .... Before the field parameters appear, we all use the solution introduced in the blog to deal with this scenario. 

Best Regards,
Community Support Team _ Jing

[047]

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:

Country	Sales_avg	Sales_median	Sales_Total

Thanks

[Pragati11]

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.

[Pragati11]

Hello @047 ,

Are you looking for something like this?

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