Total + Average columns on a table

AO3

Hello, I'm trying to have both a TOTAL column and an AVERAGE column on a table. I haven't seen this done on any card within our company so wondering if it's possible. I'll attach a screenshot of an excel table that shows what I'm trying to accomplish. Columns must be Dates until the final 2 cols… where 1 col will show the total of the previous

total_and_average.png

columns and one will show the average.

DavidChurchman

I don't know a way to do this within a single card, but it would be relatively easy to have an aggregation card next to your table card that has both of these values by category.

ArborRose

Calculation for 1/2/23:

CASE WHEN Week Start Date='1/2/23' THEN Value END

Calculation for Total:

SUM(Value)

Calculation for Average:

AVG(Value)

DataMaven

I assume he's going to build it as a pivot table, rather than creating a field for each date. The maintenance on that would be crazy.

I also don't think the average will work if there are multiple entries for a particular category per day. You would need an average daily total.

I think @DavidChurchman is right, and it's best to have a separate card with the Total and Average. I think you will need to use a fixed function for the average.

Having done the dual card thing before, it does end up working well, and it gives you an opportunity to play with the summaries and make something nice.

david_cunningham

@AO3 An option for you if you want to avoid FIXED or WINDOW functions.

You can always modify to fit your design needs.

Use a pivot table grouped by week for the weekly values and the the total.

Then you can calculate the average weekly value using a beast mode like this

SUM(value)

/

COUNT(DISTINCT CONCAT(WEEK(event_date),YEAR(event_date)))

And then build a card for the weekly average, and position these 2 cards to display next to each other like this.

The nice thing about this as @DavidChurchman and @DataMaven were saying, is that you retain the ability to sort/filter/interact with the data. FIXED and WINDOW functions can get dicey pretty quickly when you're talking about period averages of variable lengths.

Another option would be to do this inside of a summary number if you want to avoid additional cards.

Here is the Beast Mode that generated that summary number

CONCAT(

'Core Leads ',

ROUND(SUM(case when type = 'Core Leads' then value end)/COUNT(DISTINCT CONCAT(WEEK(case when type = 'Core Leads' then event_date end),YEAR(case when type = 'Core Leads' then event_date end))),0),

' | Inquiries ',

ROUND(SUM(case when type = 'Inquiries' then value end)/COUNT(DISTINCT CONCAT(WEEK(case when type = 'Inquiries' then event_date end),YEAR(case when type = 'Inquiries' then event_date end))),0)

)

DavidChurchman

I don't know a way to do this within a single card, but it would be relatively easy to have an aggregation card next to your table card that has both of these values by category.

ArborRose

Calculation for 1/2/23:

CASE WHEN Week Start Date='1/2/23' THEN Value END

Calculation for Total:

SUM(Value)

Calculation for Average:

AVG(Value)

ArborRose

Obviously I don't have your data or your structure. This was just using something mocked up. Dates would be actual dates and I would have a calculated field that would identify the week start date.

DataMaven

I assume he's going to build it as a pivot table, rather than creating a field for each date. The maintenance on that would be crazy.

I also don't think the average will work if there are multiple entries for a particular category per day. You would need an average daily total.

I think @DavidChurchman is right, and it's best to have a separate card with the Total and Average. I think you will need to use a fixed function for the average.

Having done the dual card thing before, it does end up working well, and it gives you an opportunity to play with the summaries and make something nice.

AO3

Thanks everyone for the help. I will go the route of a separate card.

Still not sure about how I can do an average? For Totals, I can get the total column easily using that built in function in Analyzer under Properties (Totals > Show Total Row). Even if it has to be a separate card entirely for Averages, there's got to be a way to ask for averages over a certain period of time rather than creating a new beastmode for each field to develop an average. @DataMaven what do you mean a fixed function?

david_cunningham

@AO3 An option for you if you want to avoid FIXED or WINDOW functions.

You can always modify to fit your design needs.

Use a pivot table grouped by week for the weekly values and the the total.

Then you can calculate the average weekly value using a beast mode like this

SUM(value)

/

COUNT(DISTINCT CONCAT(WEEK(event_date),YEAR(event_date)))

And then build a card for the weekly average, and position these 2 cards to display next to each other like this.

The nice thing about this as @DavidChurchman and @DataMaven were saying, is that you retain the ability to sort/filter/interact with the data. FIXED and WINDOW functions can get dicey pretty quickly when you're talking about period averages of variable lengths.

Another option would be to do this inside of a summary number if you want to avoid additional cards.

Here is the Beast Mode that generated that summary number

CONCAT(

'Core Leads ',

ROUND(SUM(case when type = 'Core Leads' then value end)/COUNT(DISTINCT CONCAT(WEEK(case when type = 'Core Leads' then event_date end),YEAR(case when type = 'Core Leads' then event_date end))),0),

' | Inquiries ',

ROUND(SUM(case when type = 'Inquiries' then value end)/COUNT(DISTINCT CONCAT(WEEK(case when type = 'Inquiries' then event_date end),YEAR(case when type = 'Inquiries' then event_date end))),0)

)