Total Row in Table (DAX or PowerQuery)

[Donerandparmo1]

Hi All,

Is there any easy way to add a total row to this table?:

Seems like it should be simple, but I'm having a nightmare!

Cheers.

[Natarajan_M]

Hi @Donerandparmo1 ,For sure, we can create the total as a separate row using the sum and union functions.

However, before doing that, could you please clarify why this is required at the data layer?
Specifically, why can't we leverage DAX to calculate the total at the visual level,
and what is the difference this custom total provides compared to using DAX?

Thanks 

[danextian · Accepted solution]

Hi @Donerandparmo1 

It is  either you use those column in  a table visual, append a total row in the query editor a calculated table referencing the original table with a total row

test = 
VAR Detail =
    --add all columns in the original table
    SUMMARIZECOLUMNS(
        'FactTable'[Category],
        'FactTable'[Date],
        'FactTable'[Index],
        "Revenue", SUM('FactTable'[Revenue]),
        "Transactions", SUM('FactTable'[Transactions])
    )

VAR TotalRow =
    ROW(
        "Category", "Total",
        "Date", BLANK(),
        "Index", BLANK(),
        "Revenue", CALCULATE(SUM('FactTable'[Revenue])),
        "Transactions", CALCULATE(SUM('FactTable'[Transactions]))
    )

RETURN
    UNION(Detail, TotalRow)

[Donerandparmo1]

exactly what I wanted, cheers!