Sometimes, the group feature does not work well when you need to group many data values. In these cases, you can use the If function to group your data.
For example, you may want to show revenue in a table and a pie chart for each performance type.
Each store falls into one of these categories based on business rules:
- Top Performers: Sales revenue Greater than $7,500,000
- Average Performers: Sales revenue Between $2,000,000 and $7,500,000
- Low Performers: Sales revenue Less than $2,000,000
You need to show sales revenue, including subtotals, in the table and show sales revenue breakdowns in the chart.
Start by creating a table with the state and sales revenue objects.
Next, create a Sales revenue performance dimension variable using one of the following formulas.
Add the Sales revenue performance variable to your table.
Apply a break on Sales revenue performance and insert a sum in the sales revenue column to create the first block.
To add the chart, insert a pie chart into your report.
Set the sales revenue measure as the pie sector size and the sales revenue performance variable as the pie sector color.
Modify Calculations with the If Function
You can use the If function to make calculations in your reports more flexible and dynamic.
Use the following syntax to change how calculations behave with the If function:
- =If( [object name] = "true value" ; [measure] behavior A ; [measure] behavior B)
Or
- =If [object name] = "true value" Then [measure] behavior A Else [measure] behavior B
If the value returned by an object matches the value in quotes, the calculation uses one method. If not, it uses a different method.
Watch the video to modify calculations using the If function.
Let's Summarize What You've Learned
- Use the If function to group data dynamically when manual grouping is not practical.
- Apply the If function to modify calculations based on specific business rules or conditions.
- Use correct syntax, including InList for multiple values, to ensure accurate results in your reports.