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
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors