Forum Discussion

MFR2023's avatar
MFR2023
Frequent Visitor
3 years ago
Solved

Distinct Count Customers with Filters based on Measures

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 minimu...
Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago

    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.

MFR2023's avatar
MFR2023
Frequent Visitor
3 years ago

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's avatar
lbendlin
Super User
3 years ago

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's avatar
    MFR2023
    Frequent Visitor
    3 years ago

    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's avatar
      MFR2023
      Frequent Visitor
      3 years ago

      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!