cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ajhope
Helper I
Helper I

Dynamic column in measure calculation

Hi All,

 

New to Power BI and trying to work out how to dynamically select the column from my data table to perform a calculation on. For example I have 10 columns in my dataframe and want to calcualte the average for a column and then display it in a card. 

 

I then want to be able to use a slicer to select one of the other columns in the dataframe and have the card update with the average of that column. 

 

The reason I am doing this is I need to create a dashboard which shows summary statistics for each each column in a large dataframe (but only need to view one column at a time). My intention is to have a single page with the various summary statistics (e.g. Average, STDev, Range), and to be able to select the column/feature of interest to populate it and view.

 

Any suggestions would be much appreciated!

1 REPLY 1
Anonymous
Not applicable

That's easy enough.

 

Create a table which will store all the column names you want. This will be your slicer. Then, for all the measures you want create a table that will store their names. This will be the slicer for which measures to show (this also be used in a table visual to show all the measures in one go). You need to create a version of each of the measures that works on a particular column, like SUM, AVG, STD... whatever. Then create a single measure that will use SWITCH and the name of the selected column from the slicer to make a decision which measure version to use and then use it to calculate the value. This setup will give you the freedom to even show a matrix of all the measures and all the columns in one visual.

 

Best

Darek

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.