Dynamic Measures Selection



It is simple to have multiple visuals for each measure. But what if we want to run multiple measures on one visual? And the measure selection is by using slicer:


The calculation is toggling between average, median and total CTC

There are a few bases we should cover


We have already created three measures for calculating the average, median, and total CTC:


Crore = 10000000
  
 lacs = 100000
  
 Avg CTC (Active,in lacs) = 
 AVERAGEX(
  FILTER(HRMS,HRMS[Employment Status] = "Active"),
  HRMS[Current CTC]
 )/[lacs]
  
 Median CTC (Active,in lacs) = 
 MEDIANX(
  FILTER(HRMS,HRMS[Employment Status] = "Active"),
  HRMS[Current CTC]
 )/[lacs]
  
 Total CTC (Active, in cr) = 
 SUMX(
  FILTER(HRMS,HRMS[Employment Status] = "Active"),
  HRMS[Current CTC]
 )/[Crore]
 

Step 1: List of all the measures

Add a simple table in the data model, with no relationship with any other tables. It should contain

· an index column (for sorting),

· and the name of measures (to be used in the slicer)





We can use the Enter Data option for adding a table:


Step 2:

Create a measure:


 CTC = 
 SWITCH (
  TRUE (),
  VALUES ( 'tblCTC_DAX'[Measure] ) = "Avg CTC (in lacs)", [Avg CTC (Active,in lacs)],
  VALUES ( 'tblCTC_DAX'[Measure] ) = "Median CTC (in lacs)", [Median CTC (Active,in lacs)],
 VALUES ( 'tblCTC_DAX'[Measure] ) = "Total CTC (in cr)", [Total CTC (Active, in cr)],
  BLANK ()
 )
 

Essentially, we are telling Power BI that when “AVG CTC (in lacs)” is selected in the slicer, run the measure [Avg CTC (Active, in lacs)]. So on and so forth.

Step 3: Update the visuals

Add a slicer in the page and select the Measure field from the support table:



In all the visuals, we will the measure created in the previous step [CTC].


It is a handy way of creating a useful user experience.


One set of standard visual and multiple calculations – it is easier to understand and interpret. And not to mention, it saves a lot of time and effort.



Drop Me a Line, Let Me Know What You Think

 

contact@vivran.in

+91 9871-641-146

MS EXCEL || POWER BI || POWER APPS || POWER QUERY || TABLEAU || DATA ANALYTICS || TRAINING