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