In some business scenarios, it is required to design conditional count metric in Dashboard in Strategy Web 9.4.1.
The following steps demonstrate how to do that in Strategy Tutorial project:
Create data source in Microsoft excel as shown below:
Import data into project and create dashboard. Save the dashboard.
Create new metric Average (Revenue) via Revenue>New Metric>Average as shown below:
Edit Average (Revenue) and change level to Region as shown below:
Save the dashboard and create new metric M1 with case function via Revenue>New Metric>Enter Formula as shown below:
Create new metric Sum (M1) via M1> New Metric> Sum as shown below:
Edit Sum (M1) and change level to Region as shown below:
Create new metric M2 with case function as shown below:
Remove Category, M1, Sum (M1) from grid. M2 will return number of category which has revenue less than average for each region as shown below:
Note:
If error "Invalid metric formula" pops up when designing M1, save the dashboard and try again.
Please create Sum (M1) and Average (Revenue) by choosing desired function from New Metric menu instead of directly entering formula if it is required to aggregate data from base level.
