How to do a Count based on Groupings

[nado]

Hi, 

New to this forum, my first post actually. If I need to provide more context or examples please let me know. 

I'm trying to create a dashboard that focuses on Client Retention Rate relative to various fields. These are Retention Rate by Sales Team, Retention Rate by Category/Industry, etc. etc. 

A Retained Client is a Client that spent with our business last year, and has returned and spent again with us this year. I've created a table calculation that uses a pivot_index() to compare year 2024 vs. 2023 to isolate if a client is retained, new, or lost (spent last year and not this year). 

While the large data table here is useful, I want to have a few summary tables as well to provide macro level views. 

Example:

Team	Retention Rate %
Enterprise - Retail	46%
Enterprise - CPG	34%
Enterprise - Tech	41%
Enterprise - Travel	26%

The above would repeat but for different attributes (Category/Industry as one example). 

Retention Rate is based on a count of the number of Retained Clients ('Parent Advertiser')/Retained + Lost. In other words, retention is a distinct_count of Parent Advertisers, it is not based on 'Won Revenue'. 

Team	Retention Rate %
Enterprise - Retail	Distinct Count of Retained Advertisers/(Distinct Count of Lost + Distinct Count of Retained)
Enterprise - CPG	34%
Enterprise - Tech	41%
Enterprise - Travel	26%

Does any one have any guidance or thoughts on where I can start?
 

[blue1]

Sometimes, the best solution is to simply create a Derived Table . You can created the exact logic you need in SQL and join it to the TeamID in your model.