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.

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.

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.