Matrix Custom Rows/Reference Total Row Help

[nightlocker]

Hey everyone, I had a question that I have been stuck for the past 2 days trying to solve I was hoping someone here could help with.

So the main idea is, I want to add a row below the Total row in a Matrix, and then another that has some calculations between the Total row and the second Row I added. So all in all:

A B1 B2 B3 B4 . . .

A1 .

A2          .

.                    .
.                         .

Total 4 7 9 10 . . .

R1    2 6  4   3 . . .

R2    2 1  5   7 . . .

Here the rows are A (A1, A2, A3) and columns are B (B1, B2, B3...) and both of these I get from my data set. And R1 and R2 are the custom rows, R1 being something else from the dataset, and R2 being the measure of Total - R1.

I looked everywhere on the internet, and I couldn't find a solution to this. I am trying to do something similiar to the OPAL tools in Excel, which allows you to add custom rows to Pivot Tables.

I then though, ok, if that doesn't work, maybe I can reference the Total row in another matrix, and do my measure there. And I couldn't figure out how to reference the Total row either. I also tried using DAX, but couldn't figure out something for that either.

If someone could help me out with this, I would really appreciate it. I am new to Power BI, so maybe I am missing something basic.

Thank you

[giammariam]

@nightlocker per one of @Greg_Deckler's suggestions, here's a quick vega-lite implementation that could be ported over into Deneb. Just as an example, I added Average and Max as your other calculations under Total (these can be any calculations/values). There is a lot that can be done with formatting, but just wanted to provide an implementation showing what is possible.

If you would like to move forward with Deneb, just let me know and I'd be happy to help.

gist: https://vega.github.io/editor/#/gist/21d167c226054e60c23b55be70a78ada/spec.json

Vega-Lite spec:

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "data": {
    "name": "dataset",
    "values": [
      {"B1": 2, "B2": 7, "B3": 24},
      {"B1": 6, "B2": 12, "B3": 5},
      {"B1": 15, "B2": 2, "B3": 56},
      {"B1": 15, "B2": 12, "B3": 13}
    ]
  },
  "transform": [
    {"window": [{"op": "row_number", "as": "RN"}]},
    {"calculate": "'A'+toString(datum['RN'])", "as": "Row"},
    {"fold": ["B1", "B2", "B3"], "as": ["B", "Value"]},
    {
      "window": [{"field": "Value", "as": "Total", "op": "sum"}],
      "frame": [null, null],
      "groupby": ["B"]
    },
    {
      "window": [{"field": "Value", "as": "Average", "op": "average"}],
      "frame": [null, null],
      "groupby": ["B"]
    },
    {
      "window": [{"field": "Value", "as": "Max", "op": "max"}],
      "frame": [null, null],
      "groupby": ["B"]
    }
  ],
  "spacing": 0,
  "vconcat": [
    {
      "name": "Matrix",
      "width": {"step": 30},
      "height": {"step": 30},
      "encoding": {
        "x": {
          "field": "B",
          "type": "nominal",
          "axis": {"orient": "top", "title": null, "labelAngle": 0}
        },
        "y": {"field": "Row", "axis": {"title": null}}
      },
      "layer": [
        {"mark": {"type": "rect", "stroke": "#eee", "fillOpacity": 0}},
        {"mark": "text", "encoding": {"text": {"field": "Value"}}}
      ]
    },
    {
      "name": "Total",
      "transform": [
        {"window": [{"op": "row_number", "as": "aggRN"}], "groupby": ["B"]},
        {"filter": "datum['aggRN']==1"}
      ],
      "width": {"step": 30},
      "encoding": {
        "x": {
          "field": "Total",
          "type": "nominal",
          "axis": null,
          "sort": {"field": "B"}
        },
        "y": {
          "field": "1",
          "axis": {
            "title": "Total",
            "titleAngle": 0,
            "titleAnchor": "end",
            "titleFontSize": 12,
            "titleFontWeight": "bold",
            "titleBaseline": "bottom",
            "titleY": 15
          }
        }
      },
      "layer": [
        {
          "mark": {
            "type": "rect",
            "fillOpacity": 0,
            "strokeOpacity": 0
          }
        },
        {
          "mark": {"type": "text", "fontWeight": "bold"},
          "encoding": {"text": {"field": "Total"}}
        }
      ]
    },
    {
      "name": "Sub Aggregate 1",
      "transform": [
        {"window": [{"op": "row_number", "as": "aggRN"}], "groupby": ["B"]},
        {"filter": "datum['aggRN']==1"}
      ],
      "width": {"step": 30},
      "encoding": {
        "x": {
          "field": "Average",
          "type": "nominal",
          "axis": null,
          "sort": {"field": "B"}
        },
        "y": {
          "field": "1",
          "axis": {
            "title": "Average",
            "titleAngle": 0,
            "titleAnchor": "end",
            "titleFontSize": 12,
            "titleFontWeight": "bold",
            "titleBaseline": "bottom",
            "titleY": 15
          }
        }
      },
      "layer": [
        {"mark": {"type": "rect", "fillOpacity": 0, "strokeOpacity": 0}},
        {
          "mark": {"type": "text", "fontWeight": "bold"},
          "encoding": {"text": {"field": "Average"}}
        }
      ]
    },
    {
      "name": "Sub Aggregate 2",
      "transform": [
        {"window": [{"op": "row_number", "as": "aggRN"}], "groupby": ["B"]},
        {"filter": "datum['aggRN']==1"}
      ],
      "width": {"step": 30},
      "encoding": {
        "x": {
          "field": "Max",
          "type": "nominal",
          "axis": null,
          "sort": {"field": "B"}
        },
        "y": {
          "field": "1",
          "axis": {
            "title": "Max",
            "titleAngle": 0,
            "titleAnchor": "end",
            "titleFontSize": 12,
            "titleFontWeight": "bold",
            "titleBaseline": "bottom",
            "titleY": 15
          }
        }
      },
      "layer": [
        {"mark": {"type": "rect", "fillOpacity": 0, "strokeOpacity": 0}},
        {
          "mark": {"type": "text", "fontWeight": "bold"},
          "encoding": {"text": {"field": "Max"}}
        }
      ]
    }
  ]
}

[Greg_Deckler · Accepted solution]

@nightlocker For starters, you can't add a row below a matrix total row. It's possible that you could create a disconnected table for your rows and then write a measure to return the correct calculation/information based on what row it is in. https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

You can get the total returned by a matrix by using SUMMARIZE or GROUPBY with the same grouping as your matrix and then doing an X Aggregation against it like SUMX.

Finally, you might have luck with Deneb: How To Fix Measure Totals With Deneb - YouTube