Power Query Total and SubTotal

If you need to calculate the total of all rows using power query you must use these functions directly into power query in the advanced code query:

TOTAL

Sales = Table.Column(Source, "Sales"),

Where Sales is the name of function, source is the name of previous step of powerquery and Sales is the column where we wanto to have Total. After the first Function we have to insert this formula: where ew sum the previous column:

 GrandTotal = List.Sum(Sales),

After that we have to insert the last Formula:

    TableWithShare = Table.AddColumn(Source, "Share", GrandTotal)

This formula is the last and we use it in the “In” of power query. Source is the name of previous step, not of the two previous step but the first one. “Share” is the name that we want to give to the result column. GrandTotal is the previos function.

SUBTOTAL

The calculation of subtotals requires 2 features more complex to write directly into the advance editor, but before start creating subtotals you must always create a list of values to be added, given for total “Sales = Table.Column (Source,” Sales “)”:

    Subtotals = Table.Group(Source, {"Category"}, {{"Category Sales",each List.Sum([Sales]), type number}}),   

In this function we have to identify:

  • Source: name of previous passage
  • “Category” : value that we want to create subtotals
  • “Category Sales”: me of function
  • [Sales]: the previous colum that we have created before ( for total example)

Added this function we must call another function that links the value of the subtotal to the respective row:

    JoinTables = Table.Join(Source, "Category", Subtotals, "Category"),

In this function we have to identify:

  • Source: name of previous passage ( not the previous function)
  • “Category” value that we want to create subtotals
  • Subtotals: we call the previous function
At this point you can call up directly join tables in the “in” of advance editor or create a new column to insert the calculated value
Annunci

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...