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

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