Sunday 13 November 2016

Multiple Measures with multiple aggregate functions using Parameters ( Depended Parameters)

In this case, we see changing a measure dynamically with selected measure from parameter control, and the same time we can apply the required aggregate functions to that selected measure from parameter control

For which, we need two parameters and a calculated field.

Lets us create a parameter one (1) with name " Choose Measure"

Select data type is "String"
List following values
Sales
Profit
Show in the below picture



Lets us create parameter two(2) with name "Choose Aggregate"
Select data type is "String"
List following values
Sum
Avg
Min
Max

shown in the below picture




And enable parameter control as shown in the below picture




Now, lets us create a calculated field with name "Dynamic Measure"

Case [Choose Measure]
WHEN "Sales" THEN
    IF [Choose Aggregate]= "Sum" THEN SUM([Sales])
    ELSEIF [Choose Aggregate]="Avg" THEN AVG([Sales])
    ELSEIF [Choose Aggregate]="Min" THEN MIN([Sales])
    ELSEIF [Choose Aggregate]="Max" THEN MAX([Sales])
    END
WHEN "Profit" THEN
    IF [Choose Aggregate]= "Sum" THEN SUM([Profit])
    ELSEIF [Choose Aggregate]="Avg" THEN AVG([Profit])
    ELSEIF [Choose Aggregate]="Min" THEN MIN([Profit])
    ELSEIF [Choose Aggregate]="Max" THEN MAX([Profit])
    END
END


Write the expression as below and the same shown in the below picture



And the final output is





No comments:

Post a Comment