SAP Cloud Analytics Average Calculation

[Abraham3]

I am using SAP cloud analytics to find the average profit by region using a calculated measure. However it does not show the average but only the sum.  I chose Average for operation and Aggregation Dimension as Region. See screenshot. The chart for Aggregation shows sum instead of average. Please advise. Thanks 

[Yogeshwar_M]

Hi User Please check below points 

Verify the Calculated Measure Settings:

Go to the Calculated Measure settings.
Ensure that:
Operation is set to Average.
Aggregation Dimension is correctly set to Region.
Double-check that the measure used for the calculation is numeric and supports aggregation.
Review Chart Configuration:

Open the chart settings and ensure:
The chart type is appropriate for displaying averages (e.g., Bar, Column).
No conflicting aggregations are applied at the chart level.
The measure shown is the calculated measure you created.
Check the Model’s Aggregation Behavior:

Navigate to the Model in SAC and inspect the aggregation behavior of the base measure (e.g., Profit).
The default aggregation of the base measure (sum, average, etc.) should not conflict with the calculated measure.
Set Aggregation in the Chart:

In the chart designer:
Select the calculated measure.
Ensure that the aggregation is explicitly set to Average at the chart level.

Filter Settings:

Ensure that the filters applied to the chart or story do not inadvertently affect the calculation (e.g., Region filters might lead to unintended aggregation).
Use the Formula Editor:

If the predefined settings are not working as expected, use the Formula Editor to create a custom calculation for the average.

Example:
scss
Copy code
Average_Profit = SUM(Profit) / COUNT_DISTINCT([Region])
Replace [Region] with the appropriate dimension for your dataset.
Test in a Table Visualization:

Add a table to your story and include the calculated measure alongside Region.
Check if the calculated measure is showing the correct average for each region.
Refresh Data:

Refresh the model or data source to ensure the calculated measure is updated correctly.
Check for Story-Level Overrides:

Look for any story-level custom aggregations that might be overriding your calculated measure settings.

Thanks in advance 

[Yogeshwar_M]

Hi @Abraham3, 

This is bar chart visualization for Region wise profit as per your sample superstore data

Thanks in advance 

[Abraham3]

Hi @Yogeshwar_M Can you please explain how you created the chart from the table. Thanks

[Yogeshwar_M]

Hi @Abraham3,

Table show actual value from excel,

Below is screenshot for calculation of Avg. Profit by Region.

Thanks.

[ramkumar_thayumanavan]

Hi @Abraham3 ,

Good day!!

Averaging between region will give you only single value(which is the average amount considering all the regions). So when you again add the region dimension to the chart it will any way split to individual values.

You must remove the region dimension in the chart to get the average.

Or if you want the split of regions you should be aggregating based on other dimensions.

Kind regards,

Tha.Ram

[Abraham3]

Thanks for the update @ramkumar_thayumanavan . I tried but I am still not able to see average by region. This is the superstore data https://community.tableau.com/s/question/0D54T00000CWeX8SAL/sample-superstore-sales-excelxls. I am trying to create a bar chart that shows average profit of each region. Please let me know how I can create a bar graph that shows each the region and the average profit. Thanks

[Yogeshwar_M]

Hi @Abraham3,

Aggregation operates at the parent level, which is why it displays the same value as profit. To calculate the average, we need to consider child-level members. As shown in Screenshot 1, we first calculate the total profit, and in Screenshot 2, we determine the count of regions.

The chart illustrates the average profit region-wise.

---

Let me know if you'd like further refinements

Please upvote if the answer is correct!

Thanks.