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.

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.