Excel Grand Total in PivotChart from Data Model

Question

Goal

Generate a Grand Total column (called All below) in a PivotTable to be displayed in a PivotChart in Excel 2016.

PivotTable Current

Sum of DailySales	Column Labels
Row Labels	North	South
1/2/2021	1000	2000
1/3/2021	1500	2500

PivotTable Desired

Sum of DailySales	Column Labels
Row Labels	North	South	All
1/2/2021	1000	2000	3000
1/3/2021	1500	2500	4000

Data Insight

All data is in the Data Model, with a relationship established through StoreID. Data pulls from two tables:

1. Districts
   • StoreID
   • District (North or South)
2. Sales
   • StoreID
   • DateStamp
   • DailySales

Attempts

• I tried creating a calculated item. Not possible: Grayed out.
• I tried using a measure, but it just totals each District separately, repeating values I already have.
• I looked into using Sets through an MDX expression. This seemed to hold the most promise, as I just need to return (shorthand) [North],[South],[North]+[South]. However, I have never used MDX before and can't figure out a simple sum function to accomplish this. Creating a set from columns gives me this starting point:
  • {([Districts].[District].&[North]),([Districts].[District].&[South])}

Solution I hate The only way I've figured out how to do this is to create two PivotTables and then combine them through the Wizard into a third. This creates significant overhead in my document and gets messy with updating.

Summary Any assistance is appreciated. Thanks for at least sticking with me this far.

Answer 1

Given data samples like below:

You can create a table in Power query with code:

let

//read in the data
    Source = Excel.CurrentWorkbook(){[Name="Districts"]}[Content],
    districts = Table.TransformColumnTypes(Source,{{"StoreID", type text}, {"District", type text}}),
    Source2 = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    sales = Table.TransformColumnTypes(Source2,
        {{"StoreID", type text},{"DateStamp", type date},{"DailySales", Int64.Type}}),

//Join the two tables
    joined = Table.NestedJoin(districts, "StoreID", sales,"StoreID","joined",JoinKind.FullOuter),

//expand the joined table
//then groupby date
    #"Expanded joined" = Table.ExpandTableColumn(joined, "joined", {"DateStamp", "DailySales"}, {"DateStamp", "DailySales"}),
    #"Grouped Rows" = Table.Group(#"Expanded joined", {"DateStamp", "District"}, {{"Values", each List.Sum([DailySales]), type nullable number}}),

//Pivot by District
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[District]), "District", "Values", List.Sum),

//Add a column summing the districts for each date
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "All", each List.Sum({[North],[South]}))
in
    #"Added Custom"

Looking like:

Then Load to a pivot chart, and create the chart: