Totals are shown either at the beginning or the end of the VISUAL. However sometimes it is desirable to SORT the “Total” row as well

Consider the following example of Top 20 Tennis players. Points Per Tournament is a MEASURE (Points/Tourn Played).

The average points per tournament are 181 which are shown at bottom in the Table. The Table is sorted by Points per Tournament….however the TOTAL still remains at the bottom

What we would like to achieve is this

Here is the RAW data. Try doing it yourself before you look at the SOLUTION below

Country | Player | Points | Tourn Played |

ESP | Rafael Nadal | 10,465 | 16 |

SUI | Roger Federer | 8,505 | 16 |

GBR | Andy Murray | 5,290 | 17 |

CRO | Marin Cilic | 4,505 | 22 |

GER | Alexander Zverev | 4,400 | 23 |

AUT | Dominic Thiem | 3,935 | 27 |

SRB | Novak Djokovic | 3,765 | 17 |

BUL | Grigor Dimitrov | 3,650 | 23 |

SUI | Stan Wawrinka | 3,450 | 16 |

BEL | David Goffin | 2,840 | 26 |

ESP | Pablo Carreno Busta | 2,650 | 24 |

CAN | Milos Raonic | 2,600 | 21 |

USA | John Isner | 2,550 | 24 |

USA | Sam Querrey | 2,525 | 22 |

FRA | Jo-Wilfried Tsonga | 2,490 | 20 |

JPN | Kei Nishikori | 2,475 | 20 |

RSA | Kevin Anderson | 2,470 | 21 |

CZE | Tomas Berdych | 2,230 | 21 |

ARG | Juan Martin del Potro | 2,225 | 18 |

**THE SOLUTION**

To achieve desired result, we can create a NEW TABLE which contains all the rows of the Original Table **plus **the totals of all these rows. This can be achieved using the “ROW” function

Go to Modelling Tab>>>NEW TABLE and use this formula

New Table = UNION ( ROW ( "Country", "Total", "Player", "Top 20", "Points", SUM ( TableName[Points] ), "Tourn Played", SUM ( TableName[Tourn Played] ), "Points per Tournament Played", [Points Per Tournament] ), SUMMARIZE ( TableName, TableName[Country], TableName[Player], TableName[Points], TableName[Tourn Played], "Points per Tournament Played", [Points Per Tournament] ) )

The above formula simply combines

Original Table (along with the MEASURE “Point per Tournament”)

with

a ROW of Aggregate Values for Top 20 Players

You can Download the PBIX file here