Customers with no sales ny month

[BORUCH]

Hi All

I have two tables one is my all customer table, which has my customer number ,customer name, with no sales in it.

My other table is my sales table which also has customer number, customer name, invoice number, invoice date, and amount.

There is only one relationship customer # in my customer table, and customer number in my sales table.

What I would like to accomplish is to be able to see by Month what customers had sales and what customers did not have sales I don’t want to use my customer number from my sales table, because my sales table only has customer # that have sales in it

if I grab my customer number from my customer table and the total invoice amount from my sales table I will not see which customers had no sales

I have a table called calendar but the problem is that my customer table has no column that has anything to do with dates

Any help is appreciated

 

[FlashFire]

So create a chart with the axis being Customer number or Customer name. then for the values create a measure with something like the code:

IF( COUNTA( 'othertable'[customer name]] ) = 0,
"Didn't buy anything",
"Bought something"
)

 

[BORUCH]

so create a chart with the axis being customer number or customer name. Then for the values create a measure with something like the code:

if( counta( 'othertable'[customer name]] ) = 0,
"didn't buy anything",
"bought something"
)

where do i put this measure in the sales table, or the main customer table, if i put it in the customer table everything comes up as bought something which is not true and i cant slice it by date

 

[Ozeroth]

Hi there,

I have borrowed this pattern from page 384 of The Definitive Guide to DAX

This measure gives the count of customers with no sales.

If you put customer (from the customer table) on your pivot table (with any other filters) along with this measure, the value of this measure will be 1 for those customers with no sales and blank otherwise.

If your version of DAX has the EXCEPT function (Power BI Desktop or Excel 2016):

Customers With No Sales
=
CALCULATE (
    DISTINCTCOUNT ( customer[customer number] ),
    EXCEPT (
        VALUES ( customer[customer number] ),
        CALCULATETABLE ( VALUES ( customer[customer number] ), sales )
    )
)

Or if you don't have the EXCEPT function:

Customers With No Sales
=
CALCULATE (
    DISTINCTCOUNT ( customer[customer number] ),
    FILTER (
        VALUES ( customer[customer number] ),
        NOT (
            CONTAINS (
                CALCULATETABLE ( VALUES ( customer[customer number] ), sales ),
                Customer[customer number], customer[customer number]
            )
        )
    )
)

 

[BORUCH]

hi there,

i have borrowed this pattern from page 384 of the definitive guide to dax

this measure gives the count of customers with no sales.

If you put customer (from the customer table) on your pivot table (with any other filters) along with this measure, the value of this measure will be 1 for those customers with no sales and blank otherwise.

If your version of dax has the except function (power bi desktop or excel 2016):

customers with no sales
=
calculate (
    distinctcount ( customer[customer number] ),
    except (
        values ( customer[customer number] ),
        calculatetable ( values ( customer[customer number] ), sales )
    )
)

or if you don't have the except function:

customers with no sales
=
calculate (
    distinctcount ( customer[customer number] ),
    filter (
        values ( customer[customer number] ),
        not (
            contains (
                calculatetable ( values ( customer[customer number] ), sales ),
                customer[customer number], customer[customer number]
            )
        )
    )
)

i put this measure on my main customer table but when i add month to the row section it tells me that there is no relationship

 

[BORUCH]

i put this measure on my main customer table but when i add month to the row section it tells me that there is no relationship

Let me explain a Little more i have two tables A sales table which has my customer #, invoice number, invoice Date, credit memo number, credit amt, etc.
Then i have my customer table which has all of my customers, their address, city, state, etc.

What i need is, A formula that takes the customer number in the main customer table and searches in the sales table for that number and tell me if there is any sales. I would like to be able to see it by month just like i can slice my sales table by month

My goal is to see for ex. that in the month of Jan 2016 customer #A111 customer#B111 had sales and customer #C111 and customer#D111 had no sales

 

[Ozeroth]

Good, that's what I thought you wanted.

The above measure will achieve that if used appropriately in a pivot table (since blank rows/cols are removed by default).
Plus you will need a similar measure for customers with sales.

What version of Excel are you using or Power BI? I will post a small example illustrating how to do this with the above measure & the customers with sales measure.

 

[BORUCH]

good, that's what i thought you wanted.

The above measure will achieve that if used appropriately in a pivot table (since blank rows/cols are removed by default).
Plus you will need a similar measure for customers with sales.

What version of excel are you using or power bi? I will post a small example illustrating how to do this with the above measure & the customers with sales measure.

i'm using excel 2016 latest build

 

[Ozeroth]

Hi again,

Here is a dummy PowerPivot model showing some ways of handling this:
https://www.dropbox.com/s/6bsjeezuv3o7qxs/Customers%20without%20Sales.xlsx?dl=0

Examples on the Pivot tab. Try changing the Month slicer.

After rereading your post, I included a measure similar to FlashFire's (as well as the ones I suggested earlier). Maybe you prefer that method.

So the measures are:

1. Customers With Sales: Counts # customers with sales. If Customer Number is filtered, then it evaluates to 1 for customers with sales, otherwise (blank), which hides customers without sales.
2. Customers Without Sales: Same as above but customers without sales. Uses EXCEPT function which will work in Excel 2016.
3. Sales Flag: If the Sales table is nonempty in the current filter context, returns "Had Sales", otherwise returns "Had No Sales". This can actually be used for any dimension(s), not just Customer.

The first two measures can also be adapted to evaluate any expression in the context of customers with/without sales.

Hopefully one of the methods is similar to what you're looking for.

Owen

 

[BORUCH]

Hi again,

Here is a dummy PowerPivot model showing some ways of handling this:
https://www.dropbox.com/s/6bsjeezuv3o7qxs/Customers%20without%20Sales.xlsx?dl=0

Examples on the Pivot tab. Try changing the Month slicer.

After rereading your post, I included a measure similar to FlashFire's (as well as the ones I suggested earlier). Maybe you prefer that method.

So the measures are:

1. Customers With Sales: Counts # customers with sales. If Customer Number is filtered, then it evaluates to 1 for customers with sales, otherwise (blank), which hides customers without sales.
2. Customers Without Sales: Same as above but customers without sales. Uses EXCEPT function which will work in Excel 2016.
3. Sales Flag: If the Sales table is nonempty in the current filter context, returns "Had Sales", otherwise returns "Had No Sales". This can actually be used for any dimension(s), not just Customer.

The first two measures can also be adapted to evaluate any expression in the context of customers with/without sales.

Hopefully one of the methods is similar to what you're looking for.

Owen

Thanks a million that worked perfectly

I would just need one small modification on my sales table I have invoices and credit memos I would have to add a condition that has sales is only if invoice amount is greater the 0 to which measure do I add this to?