Knowledge Drop

Is it possible to calculate row totals without a pivot?

  • 6 April 2021
  • 0 replies
  • 398 views

Userlevel 5

Last Tested: Nov 23, 2018

 

The first instinct for users who are familiar with excel is to do (sum(${field1},${field2},${field3}))
but you will notice that the result is not as expected and it does not sum the values in that row().
 

So, what is the difference?

Excel sum does row specific total i.e, field1+field2+field3. However, when you use table calculation, it sums up across all the rows for the three columns specified. So it is a sum of all results for that field.
 

Is there a workaround?

There is a workaround that's a bit of a headache due to the amount of hardcoding.
Take these results:

245dc268-7313-4edf-ab0c-36942b01dc93

If you want to perform aggregation across the measures (therefore calculating a row total), then you can use the following format:

sum(list(${field1},${field2},${field3}))

 

I.e.:

sum(list(${order_items.count},${orders.count},${products.count}))

 

This is an edge use case since measures typically perform different forms of aggregation (SUM,AVG, etc.) and it doesn't usually make sense to perform post-query aggregation of, say, a SUM and an AVG. However, for whatever reason you may have, it's an option.

 

This content is subject to limited support.                

 


0 replies

Be the first to reply!

Reply