Distinct Count Customers with Filters based on Measures

[MFR2023]

Hello,

I've only been using Power BI and Dax for a few months and I've reached the stage of having very specific questions 

I'm trying to filter a distinct count of customer names by a minimum revenue amount over a selected period to get a total I can use to create a percentage of retained customers. 

I have been able to create filters that will filter tables within the desktop dashboard:

1. Customers must have sales over a threshold (ex 100k) in a selected period (8 quarters)

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.

The numerator is filters 1 and 2 for customers in the most recent 4. Denominator is filters 1 and 2 for the first 4.

Example data

Customer Name	Sales 1st 4Q	Sales 2nd 4Q	Sales all 8
A	0	250000	250000
B	55000	665000	720000
C	120000	0	120000
D	75000	65000	140000
E	35000	50000	85000
F	8850	987560	996410
G	580000	0	580000

Filter 1 should leave me with 6 customers. Filter 2 will give me 5 customers in 1st 4Q and 4 in 2nd 4Q

Numerator should be = 4 and denominator will be 5 giving me an 80%

I've tried Calculate(), which has issues with boolean logic, such as revenue greater than 100K, which led me to put revenue in a Filter(). I still haven't gotten a number out of it.

Note- the 4 quarter revenue and 8 quarter revenue are derived by measures as my actual revenue is based on individual orders that needs to be summed up. I do want to keep these measures as I want to be able to change the 8 sequential quarters to show progress over time.

Any assistance is appreciated.

[MFR2023]

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.

[lbendlin]

yes, as I mentioned - Change the first row according to your measure names.

[MFR2023]

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.

[lbendlin]

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

[MFR2023]

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 the 

return DIVIDE(SUMX(b,[2nd c]),SUMX(b,[1st c]),0)

[lbendlin]

yes to the pre-edit question. Not sure where you are going with the edit question. would the value ever be less than 0 ?

[MFR2023]

Thank you so much for the help!

Okay so I updated the formula to be:

var b = ADDCOLUMNS(filter(a,[All 8]>=100000),"1st c",if([1st 4Q]>0,1,0),"2nd c",if([2nd 4Q]>0 && [1st 4Q]>0,1,0))

Is that the right context?

The edited question was referring to how we were originally doing the formula manually. We would get list 2 and list 3 and determine New customers (in 3 but not 2) and Lost customers (in 2 but not 3) and confirm they were not counted as part of retained. The retained should not be less than 0, but I did not know if creating a variable noting the New and Lost and subtracting them from the return would be effective. Looking at the formula, that the numerator requires both 2 and 3 to be greater than 0 should make it work.

[MFR2023]

I have gotten it to work. My issue is my measures around my first four and second four revenue. I am using a base of Revenue CQ = CALCULATE(SUM(revenue), DATESQTD(Calendar))) to setup the current period and then using DATEADD to get the additional 7 quarters: PQ = Calculate(Revenue CQ, DATEADD(Calendar,-1,Quarter)).

This was screwing up the revenue being selected as the Summarized table wasn't getting the right time period. When I hardcoded the time periods using DATESINPERIOD() my numbers came out correct. I'll be working on getting the First 4, Second 4, and All 8 measures to work so I can choose the time period through a slicer.

Thank you lbendlin for your assistance!

[MFR2023]

Great! It appears to work, thank you so much. I will add this topic on my list of things to learn more about.

[lbendlin · Accepted solution]

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.