Table Card - Total to be % calculation rather than SUM, rows by week

Jbrorby

Hello,

I've attached an excel workbook showing a sample bit in its raw form, what I want to get out of that, and what I am getting.

I'm primarily having two issues:

1. Getting everything on 1 row by fiscal week. I can accomplish this until I add a percentage beastmode, then the rows will no longer be condensed to 1 row per week. It changes to 1 row per calculation.
2. Getting the table total to function like the beastmode, taking the total performed divided by the total projected, rather than a sum of the beastmode rows.

One thing I tried was modifying my beastmode to a partition by week, recruiter, rather than "performed"/"projection"

This was helpful in the sense that, even after the rows became uncondensed, it still gave the % by week, rather than by row. But this still leaves me with uncondensed rows and doesn't help my total row issue.

The other thing i tried was modifying the beastmode to only partition the denominator by week and recruiter. Then, I could just collapse the rows for week % totals. But this also did not resolve my final, bottom total row. The final bottom total row still SUMs.

This would be a lot easier to accomplish if they did not need it in a table card and I could use bar graphs and separate cards for week total and year total, etc., but thats how they want it so I am doing my due dilligence.

hs_Drive_non-grouped_De-duped (1).xlsx

They're also going to want to be able to filter by different procedure codes, so that is why I'm hesitant to attempt this in ETL with group bys or rank and windows

I hope this makes sense

GrantSmith

You just need to utilize aggregates in your beast mode to add all of the elements in the group before determining the percentage.

SUM(`performed`) / SUM(`projected`)

You can then format that as a percentage to display correctly.

The problem with the Total Row column is that it adds everything together so if you're at 80% for both weeks it'll think it's 160% instead of the 80% across the total.

You could try and union / stack your data such that there is a "Total" recruiter the the aggregated data but that won't allow you to see those total records if you're filtering on a specific recruiter.

GrantSmith

You just need to utilize aggregates in your beast mode to add all of the elements in the group before determining the percentage.

SUM(`performed`) / SUM(`projected`)

You can then format that as a percentage to display correctly.

The problem with the Total Row column is that it adds everything together so if you're at 80% for both weeks it'll think it's 160% instead of the 80% across the total.

You could try and union / stack your data such that there is a "Total" recruiter the the aggregated data but that won't allow you to see those total records if you're filtering on a specific recruiter.