Forum Discussion
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
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