Search code examples
daxpowerquerypowerpivot

Create calculated column based on aggregated column in pivot table

Presume I have the following data:

Country Type Entity Score
Country1 Type1 Entity1 1
Country2 Type2 Entity1 2
Country3 Type3 Entity1 3
Country4 Type4 Entity2 4
Country5 Type5 Entity3 5

I need to override the Entity column where the aggregation of Score is greater than a threshold. The aggregation context will be provided in a pivot table.

I am not sure how, or even if, this is possible. I have tried adding a calculated column in the Power Pivot editor using CALCULATE and SUMX, but this does help with filter context from the output pivot table. I have also tried adding a measure, but you are not able to return a string in this (aggregation).

Desired pivot table output (example where threshold >= 5):

Row Labels Sum of Score
Entity1 6
Entity3 5
Other 4
Grand Total 15
Solution

  • With Powerquery you will get the following result

    enter image description here

    with this M-Code

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    chType = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Type", type text}, {"Entity", type text}, {"Score", Int64.Type}}),
    grpRows = Table.Group(chType, {"Entity"}, {{"Sum Score", each List.Sum([Score]), type nullable number}}),
    fltRows_GE5 = Table.SelectRows(grpRows, each [Sum Score] >= 5),
    tbl = Table.InsertRows(fltRows_GE5,Table.RowCount(fltRows_GE5),{[Entity = "Others", Sum Score = List.Sum(Table.SelectRows(grpRows, each [Sum Score] < 5)[Sum Score])]}),
    result = Table.InsertRows(tbl,Table.RowCount(tbl) , {[Entity = "Total",Sum Score = List.Sum(tbl[Sum Score])]})
in
    result