Forum Discussion

Donerandparmo1's avatar
Donerandparmo1
Helper I
5 months ago
Solved

Total Row in Table (DAX or PowerQuery)

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.

Need Help
  • danextian's avatar
    danextian
    5 months ago

    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)

     

3 Replies

  • danextian's avatar
    danextian
    Super User
    5 months ago

    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)

     

  • Natarajan_M's avatar
    Natarajan_M
    Super User
    5 months ago

    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