Tricky Distinct Count Problem - PowerPivot

JimMatthews

New Member
Joined
Aug 12, 2014
Messages
9
I have a distinct count problem I can't get my head around. See the following data model image


I have a simple distinctcount (sales[customer number]) that tells me how many customers there are.
I can create a pivot table and put "register type" in rows and distinct count in values. This tells me how many customers purchased from each register type.
What I want to do is find out how many customers DID NOT purchase from each register type (customers can by from none or more register types)

So I am thinking I need something like this.

Customers not purchased from Type 1
Code:
=calculate ([Total Customers],
    filter(Customers,
        calculate(RegisterType[Type]=1) &&
        countrows(Sales)=0
    )  
)
But I can't get it to work.

Any ideas out there?
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
Data model pictures didn't come through.

I'm wondering if =CALCULATE([Total Customers], ALL(RegisterType)) - [Total Customers] would give you what you want?
 

JimMatthews

New Member
Joined
Aug 12, 2014
Messages
9
Hi Scott

I don't think that will do it. Take an extreme example. I have 100 customers. 95 of them have purchased through register 1 and 90 of them have purchased through register 2. If I were doing this in RDBMS I would create a query, join the list of all customers with the list for register 1, and find out the 5 customers that didn't purchase through that register. Repeat for register 2. You will end up with 2 lists, and there is no guarantee that there will be any customers on both lists.

Sorry about the link. Try this https://www.dropbox.com/s/07r7jhby6uj9xse/distinct count problem.png?dl=0

So I am thinking this is a bit like a many to many problem. I am trying to push a filter that iterates the customer table and checks all customers to see if they are "absent" as a register 1 customer. So in the filter, I first filter for register type =1 (this should flow through to the sales table giving me a list of all customers that "HAVE" purchased through register 1. Then my Calculate(countrows(sales))=0 kicks in to see if there are any records. zero records means they have never purchase from this register. So it would be like this.

Code:
=CALCULATE([Customer Count],
      filter(
           Customers,
          RegisterType[Type]=1 &&
           CALCULATE(
               COUNTROWS(sales)
           ) = 0
         
       )
)


Trouble is I get this error

Calculation error in measure 'Sales'[Customers Never Buying Delivery]: The value for column 'Type' in table 'RegisterType' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified.

so this is telling me that I have no row context with RegisterType[Type], which makes sense. So then I tried to push this part of the formula back up the stack, and tried this.

Code:
CALCULATE(
     CALCULATE([Customer Count], 
          RegisterType[Type]<>"1"
     ) &&     filter(
          Customers,
          CALCULATE(
               COUNTROWS(sales)
           ) = 0   
       )
)

I kind of like this formula (except it returns blank). The first portion returns the count of customers that have purchased on any register except 1 - so that is good. But then it falls over itself.

Then I tried this.


Code:
=CALCULATE([Customer Count],
      filter(RegisterType,
            RegisterType[Type]<>"1"
      ),
            filter(
                Customers,
                CALCULATE(COUNTROWS(sales)) = 0
            )
)

Also returns blank.

What I think I need is this
Code:
=CALCULATE([Customer Count],
      filter(RegisterType,
            RegisterType[Type]<>"1"  &&
            filter(
                Customers,
                CALCULATE(COUNTROWS(sales)) = 0
            )
      )
)
but DAX doesn't like it. It says

Calculation error in measure 'Sales'[Customers Never Buying Delivery]: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

So back to a statement of the problem. I want to count all customers that when a filter is applied to the RegisterType[Type]= 1, then counts all customers that don't have a record in the sales table.
 

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
I'm sure we can work this out :) You seem to be flirting all over the place with a good solution. Time to bring the date home for a night cap ;)

Can you give me a small snippet of expected output? I want to be sure I understand what you mean by "find out how many customers DID NOT purchase from each register type"

Do you want a specific measure for JUST RegisterType=1? Or was that just... trying to get something to work?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,360
Messages
5,528,237
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top