Dynamic calculation based on Slicer Selection

[ncbshiva]

Hi Team

I have two measures with V1 and V2 and two slicers as shown below.Slicer 1

Slicer 2I need to do some calculations when i select something from Slicer 1. Example as shown below with DAX formula.

1.Default = SUM(V1)

2.Ratio= SUM(V1)/SUM(V2)

FormulaValue = SWITCH(

SELECTEDVALUE([Name]);

"Ratio";

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

"Default";

CALCULATE(SUM(UnitValue[V1]));

Similarly when i select the Slicer 2 along with anything selected from Slicer 1. Example as shown below

1.Avg = (FormulaValue)/DISTINCTCOUNT(Name)

2.Sum = (FormulaValue).

My DAX formula will work only when i use slicer 1 and when i use both 1 and 2 slicer together , i am getting results only for some combinations. i am not getting results when something is selected in slicer 1 and "Sum" is selected in slicer 2 for example.

Please help me in this issue.

Regards.

[Anonymous]

@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]

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 · Accepted solution]

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

Ratio	AVG
Ratio	SUM
Default	AVG
Default	SUM

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.