Forum Discussion

ncbshiva's avatar
ncbshiva
Advocate V
7 years ago
Solved

Dynamic calculation based on Slicer Selection

Hi Team   I have two measures with V1 and V2 and two slicers as shown below.   I need to do some calculations when i select something from Slicer 1. Example as shown below with DAX formul...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    If I understand correctly you want to change measures according to slicer?

     

    You have to create a measure dimension looking something like this:

     

     

    You need to create the following measures seperatly:

     

    Ratio := CALCULATE(SUM(UnitValue[V1]))/CALCULATE(Sum(UnitValue[V2]))
Default := CALCULATE(SUM(UnitValue[V1])
    AVGR :=[Ratio]/DISTINCTCOUNT(Name)
    AVGD := [Default]/DISTINCTCOUNT(Name)

    Then you need a measure dimension to switch to the correct measure. The dimension would look something like this:

     

    slicer1        slicer2

    RatioAVG
    RatioSUM
    DefaultAVG
    DefaultSUM

     

     

    Measure Selection = 
SWITCH( TRUE(),
	VALUES('Measure Dimensions'[slicer1]) = "Ratio" && VALUES('Measure Dimensions'[slicer2]) = "AVG", [AVGR],
	VALUES('Measure Dimensions'[slicer1]) = "Ratio" && VALUES('Measure Dimensions'[slicer2]) = "SUM", [Ratio],
	VALUES('Measure Dimensions'[slicer1]) = "Default" && VALUES('Measure Dimensions'[slicer2]) = "AVG", [AVGD],
            VALUES('Measure Dimensions'[slicer1]) = "Default" && VALUES('Measure Dimensions'[slicer2]) = "SUM", [Default]
	BLANK())

     

    I think that this works properly.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

ncbshiva 

 

Measure=

VAR Opt1 = SELECTEDVALUE([Slicer1]) 

VAR Opt2 = SELECTEDVALUE([Slicer2])

VAR FormulaValue=IF (Opt1="Ratio";

                                                CALCULATE(SUM(UnitValue[V1]))/CALCULATE(Sum(UnitValue[V2]));

                                                CALCULATE(SUM(UnitValue[V1])))

 

RETURN

IF (Opt2="Avg";(FormulaValue)/DISTINCTCOUNT(Name);

       IF (Opt2="Sum";FormulaValue))

 

Hope this will work for you.

 

  • ncbshiva's avatar
    ncbshiva
    Advocate V
    7 years ago

    hI Anonymous,

     

    I am not getting values for any selection from Slicer 1 and 2 . I am getting just blank value.

    Below is my DAX

     

    Measure =
    VAR Formula = SELECTEDVALUE(DefFormula[Name])
    VAR UnitAgg = SELECTEDVALUE(DefUnitAggr[Name])
    VAR FormulaValue = IF(Formula="Default";SUM(UnitValue[V1]);
    IF(Formula="Ratio";(SUM(UnitValue[V1])/SUM(UnitValue[V2]))))
    RETURN
    IF(UnitAgg="Avg";(FormulaValue/DISTINCTCOUNT(DefFormula[Name]));
    IF(UnitAgg = "Sum";FormulaValue))
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      If I understand correctly you want to change measures according to slicer?

       

      You have to create a measure dimension looking something like this:

       

       

      You need to create the following measures seperatly:

       

      Ratio := CALCULATE(SUM(UnitValue[V1]))/CALCULATE(Sum(UnitValue[V2]))
Default := CALCULATE(SUM(UnitValue[V1])
      AVGR :=[Ratio]/DISTINCTCOUNT(Name)
      AVGD := [Default]/DISTINCTCOUNT(Name)

      Then you need a measure dimension to switch to the correct measure. The dimension would look something like this:

       

      slicer1        slicer2

      RatioAVG
      RatioSUM
      DefaultAVG
      DefaultSUM

       

       

      Measure Selection = 
SWITCH( TRUE(),
	VALUES('Measure Dimensions'[slicer1]) = "Ratio" && VALUES('Measure Dimensions'[slicer2]) = "AVG", [AVGR],
	VALUES('Measure Dimensions'[slicer1]) = "Ratio" && VALUES('Measure Dimensions'[slicer2]) = "SUM", [Ratio],
	VALUES('Measure Dimensions'[slicer1]) = "Default" && VALUES('Measure Dimensions'[slicer2]) = "AVG", [AVGD],
              VALUES('Measure Dimensions'[slicer1]) = "Default" && VALUES('Measure Dimensions'[slicer2]) = "SUM", [Default]
	BLANK())

       

      I think that this works properly.