Sometimes the Group function is not suitable for grouping data values, such as when you have too many values to select manually. In this situation, you can group by using the If() formula function.
For example, if you need to display revenue totals in both a table and a 3D pie chart aggregated for each of the eFashion performance types. The three performance types are Top Performers, Average Performers, and Low Performers.
Business Rules for Sales Revenue Performance
Each store is assigned to the appropriate category based on the following business rules:
|Sales Revenue Performance||Sales Revenue|
|Top Performers||Greater than $7,500,000|
|Average Performers||Between $2,000,000 and $7,500,000|
|Low Performers||Less than $2,000,000|
You need to display revenue figures including subtotals in the table and revenue breakdowns in the chart.
Begin by creating a table using the State and Sales revenue objects.
Next, create a Sales Revenue Performance dimension variable using one of the following formulas:
=If([Sales revenue] > 7500000 ; "Top Performers" ;
If([Sales revenue] Between (2000000 ; 7500000) ; "Average Performers" ; "Low Performers"))
=If [Sales revenue] > 7500000 Then "Top Performers"
ElseIf [Sales revenue] Between (2000000 ; 7500000)
Then "Average Performers"
Else "Low Performers"
If you add Sales Revenue Performance to the report, apply a break on Sales Revenue Performance, and insert Sum on the Sales revenue column, you create the first block.
To insert the chart, insert the 3D Doughnut chart into the report, and insert the Sales revenue measure on the Y-axis and the Sales Revenue Performance dimension on the X-axis.