Pivot table: Change Subtotal to Average

RayRay_H

Hi everyone,

I am facing a problem of presenting averages as the 'Subtotal' instead of the sum as subtotal in pivot table. The following picture is the context of this issue:

The columns are working days and the rows are monthly data for shop 1,2,3….

The source data is daily data. However, for this table, each cell is filled with the calculated sales growth rate for each month. For example, Jan-23 Shop 1's growth rate is calculated as: ( sum(Sales in Jan 2023) - sum(Sales in Jan 2022)) / sum(Sales in Jan 2022).

For the subtotal row, I want to show the average of the Growth Rate for all Monday's instead of sums.

The growth rate calculation is written in Beast mode. I read from some domo expert saying that if the beast mode is written in Average, then the 'subtotal row' will be average too. However, what I am calculating is a growth rate, so does anyone know how can I show average numbers to replace the sum numbers?

Thank you in advance for the help.

brycec

This might be able to be achieved using a FIXED function in your Beast Mode. Here's the documentation for FIXED functions in Beast Modes:

https://domo-support.domo.com/s/article/4408174643607?language=en_US.

It would probably look something like this:

AVG((sum(Sales in Jan 2023) FIXED (BY Shop) - sum(Sales in Jan 2022) FIXED (BY Shop)) / sum(Sales in Jan 2022) FIXED (BY Shop))

I haven't tested this, you'll likely have to play around with the functions a bit to figure it out.

brycec

I don't think so, but maybe someone else knows better than I do. Domo only allows one aggregation level, and two when using FIXED. You can try to change the options for that column at the top (where you normally format the value), and see if you can change the total option to SUM. But, I'd assume that would cause three levels of aggregations and would make the card fail.

brycec

This might be able to be achieved using a FIXED function in your Beast Mode. Here's the documentation for FIXED functions in Beast Modes:

https://domo-support.domo.com/s/article/4408174643607?language=en_US.

It would probably look something like this:

AVG((sum(Sales in Jan 2023) FIXED (BY Shop) - sum(Sales in Jan 2022) FIXED (BY Shop)) / sum(Sales in Jan 2022) FIXED (BY Shop))

I haven't tested this, you'll likely have to play around with the functions a bit to figure it out.

RayRay_H

@bryced Thank you very much for your comment and solution. The average works by adding both Day and Shop column in the fixed by function. However, both row subtotal and column subtotal are showing the average.

Is there any way to have subtotal row as average but total column as the aggregated sum?

brycec

I don't think so, but maybe someone else knows better than I do. Domo only allows one aggregation level, and two when using FIXED. You can try to change the options for that column at the top (where you normally format the value), and see if you can change the total option to SUM. But, I'd assume that would cause three levels of aggregations and would make the card fail.

RayRay_H

https://community-forums.domo.com/main/discussion/comment/93514#Comment_93514

Thank you for your answer. I tried and I also can't make it work. Maybe this is a DOMO limitation.