Total + Average columns on a table

AO3
AO3 Member
in Cards, Dashboards, Stories

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.

Tagged:

Best Answers

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    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.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • ArborRose
    ArborRose Coach
    Answer ✓
    image.png

    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)

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • DataMaven
    DataMaven Coach
    Answer ✓

    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.

    DataMaven
    Breaking Down Silos - Building Bridges
    **Say "Thanks" by clicking a reaction in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • david_cunningham
    david_cunningham Coach
    edited April 30 Answer ✓

    @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.

    image.png

    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.

    image.png

    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.

    image.png

    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)
    
)

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

Answers

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    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.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • ArborRose
    ArborRose Coach
    Answer ✓
    image.png

    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)

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • ArborRose
    ArborRose Coach

    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.

    image.png

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • DataMaven
    DataMaven Coach
    Answer ✓

    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.

    DataMaven
    Breaking Down Silos - Building Bridges
    **Say "Thanks" by clicking a reaction in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • AO3
    AO3 Member

    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
    david_cunningham Coach
    edited April 30 Answer ✓

    @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.

    image.png

    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.

    image.png

    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.

    image.png

    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)
    
)

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

Categories