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

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.

3 Replies

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