Forum Discussion
Distinct Count Customers with Filters based on Measures
Welcome to the world of variables and aggregator functions!
Ratio = var a = SUMMARIZE('Table',[Customer Name],"1st 4Q",sum('Table'[Sales 1st 4Q]),"2nd 4Q",sum('Table'[Sales 2nd 4Q]),"All 8",sum('Table'[Sales all 8])) var b = ADDCOLUMNS(filter(a,[All 8]>=100000),"1st c",if([1st 4Q]>0,1,0),"2nd c",if([2nd 4Q]>0,1,0)) return DIVIDE(SUMX(b,[2nd c]),SUMX(b,[1st c]),0)Change the first row according to your measure names.
Thank you for the help. The only confirmation I need is if this section :
""1st 4Q",sum('Table'[Sales 1st 4Q]),"2nd 4Q",sum('Table'[Sales 2nd 4Q]),"All 8",sum('Table'[Sales all 8]))"
can function as measures instead of summed data fields. I'd like this to be selectable based on the quarter selected. I do have a functioning date table.
yes, as I mentioned - Change the first row according to your measure names.
Great! It appears to work, thank you so much. I will add this topic on my list of things to learn more about.
Apologies, I have a follow up.
I'm trying to verify the numbers and I am getting stuck. The first variable creates a table for customer name with the 1st four quarters, 2nd four quarters, and 8 quarters revenue are tied together. The second variable creates a filter reduce the first variable down. The final formula takes the filtered table and compares any customer that had greater than 0 revenue in the 2nd 4 quarters and divides it by any customer that had greater than 0 revenue in the 1st 4.
Do I have that right?
My manual numbers are coming out different than the formula.
2. Customers must have sales in the first 4 quarters (2021 for example) and the most recent 4 quarters (2022 for example) of greater than 0.This requirement somewhat conflicts with the next explanations so I re-interpreted it as "Count all customers that had sales in the first 4 quarters, and independently count all customers that had sales in the last four quarters". Taken literally, your requirement would always result in 100%.
Ah, okay. The intention is to get a list (1) of customers that over 8 quarters had greater than a minimum dollar amount. Then a list (2) of customers in the first 4 quarters with sales greater than 0 and a list (3) of customers in the second four quarters with sales greater than 0. We only care about customers in list 2 and 3 that were in list 1. Of those customers remaining in lists 2 and 3, how many that were in 2 that made it to 3. Then we divide the number of customers in 3 by the number of customers in 2.
In that situation, would the ""2nd c",if"2nd c",if([2nd 4Q]>0,1,0))" formula also need to include
"[1st 4Q]>0"?
Edit:
OR would I need to include two new variables such as:
"Lost", if([1st 4Q]>0 && [2nd 4Q]<=0,1,0), "New",if([1st 4Q]<=0 && [2nd 4Q]>0,1,0)) and subtract those from thereturn DIVIDE(SUMX(b,[2nd c]),SUMX(b,[1st c]),0)