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:

The name of the table in this example is tblCTC_DAX

Step 2:

Create two measures:


 Measure Selection = 
  MIN(tblCTC_DAX[Index])
  
 CTC = 
 SWITCH (
 [Measure Selection],
    1, [Min CTC (Active,in lacs)],
    2, [Median CTC (Active,in lacs)],
    3, [Total CTC (Active, in cr)],
 )
 

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. If nothing is selected in the slicer, it will show the minimum CTC by default.

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 use 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.



Related article: SWITCH DAX Measures in Power Pivot


Also see: Show or Hide Measures using Slicers


@imvivran


Drop Me a Line, Let Me Know What You Think

 

contact@vivran.in

+91 9871-641-146

Join WhatsApp group: BI Simplified

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