How to show Grand Total as a SUM in a PowerPivot Column that contain averages

0
votes

I have a PowerPivot model in Excel 2013 and one of its columns (Average Tourist Arrivals) has average figures as its contents. As such, the "Grand Total" is also an "average".

I want to keep the contents of that column as average but I want the Grand Total to show the SUM of the figures contained in that column.

Is this possible? Is there a calculated measure or DAX formula that will allow me to do this?

Row Labels         Sum of Pax   Average of Tourist Arrivals
 France               624            17641
 United Kingdom       1089           12360
 Germany              270            3738
 South Africa         479            7694
 Reunion              1160           17214
 Czech Republic       39             212
 India                261            5077
 **Grand Total          3922           12617**

I want my PowerPivot to look like this, with the Grand Total of the third column being 63936:

Row Labels         Sum of Pax   Average of Tourist Arrivals
 France               624            17641
 United Kingdom       1089           12360
 Germany              270            3738
 South Africa         479            7694
 Reunion              1160           17214
 Czech Republic       39             212
 India                261            5077
 **Grand Total          3922           63936**
1
excelexcel-formuladaxpowerpivot

1 Answers

0
votes

to create a calculated field (measure....not a column) that calculates differently if at a particular granularity vs. the grand total, you have to add logic to your calculated field.

In this scenario, I am not sure how your average is calculated so I will show sample code that will need to be adjusted to that of the proper fields in your model. Essentially, you check to see if the data is filtered in some manner (i.e. ISCROSSFILTERED(Table1[Country]))....if so, calculate the average....if not, calculate the sum:

IF( ISCROSSFILTERED(Table1[Country]), AVERAGEX(Table1, DIVIDE( Table1[Tourists], Table1[Pax], 0)), SUMX(Table1, DIVIDE( Table1[Tourists], Table1[Pax], 0)))