cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PBISean Regular Visitor
Regular Visitor

Customers who made sales in ALL of selected years

Hi,

 

I had a challenge figuring out how to show all the customers who had made sales in ALL of the user selected years.

 

The source data is simple: 3 columns: a) customer name, b) sales, c) the year of the sales. I want to have a page that user can select years available (as many as they want, not neccearily consecutive years) from a slicer, and then the table below will show all the customer names who have made sales in ALL of those user selected years.  If a customer made sales only in some or none of the years selected, this customer won't show up in the table. 

 

The source data is uploaded here.

 

I tried to use "INTERSECT" and even nested "INTERSECT" functions as illustrated by Sam McKay, but the problem is: I don't know how many years users will select (the number of years users can select is NOT fixed). therefore, I don't know how many times I have to nest the "INTERSECT".  In the sample file, we have only five years (2015-2019). But in my real data source, I have total of 25 years. 

 

Thank you for help.

 

PBISean

4 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User
Super User

Re: Customers who made sales in ALL of selected years

@PBISean 

 

Please see attached file as well

 

PBISean.png

Super User
Super User

Re: Customers who made sales in ALL of selected years

Hi @PBISean 

 

Thanks Sean for the coffee offer Smiley Happy

Try this MEASURE as VISUAL level filter
Please see attached file as well

 

SalesofRepeatCustomers =
VAR noofyears =
    COUNTROWS ( ALLSELECTED ( Sales[Year] ) )
RETURN
    IF (
        SELECTEDVALUE ( Sales[Customer] )
            IN FILTER (
                ALLSELECTED ( Sales[Customer] ),
                VAR mytable =
                    CALCULATETABLE ( VALUES ( Sales[Year] ) )
                RETURN
                    COUNTROWS ( FILTER ( mytable, CALCULATE ( SUM ( Sales[Sales] ) <> 0 ) ) ) = noofyears
            ),
        1
    )

I get the following result

pbisean_.png

Super User
Super User

Re: Customers who made sales in ALL of selected years

Hi @PBISean 

 

I will look at the attachment and get back to you

 

Regards,

Zubair

Super User
Super User

Re: Customers who made sales in ALL of selected years

Hi @PBISean 

 

My apologies for late reply.

 

In the question tab, you can use this MEASURE. I have highlighted the changes from your formula in red color.
KEEPFILTERS will ensure that all context filters from inside the Matrix come into play

 

SalesofRepeatCustomersCalc =
CALCULATE (
    [Total Sales],
    KEEPFILTERS (
        FILTER (
            ALLSELECTED ( Sales[Customer] ),
            COUNTROWS (
                FILTER ( ALLSELECTED ( Sales[Year] ), CALCULATE ( SUM ( Sales[Sales] ) <> 0 ) )
            ) = [CountSelectedYears]
        )
    )
)

 

The earlier version of the MEASURE was created to work as  VISUAL filter. Thats why you dont see it in the Magic tab

PBIsean1.png

 

12 REPLIES 12
Super User
Super User

Re: Customers who made sales in ALL of selected years

Hi @PBISean 

 

Try this MEASURE

Do you need output of the customers in a card visual?

 

 

Measure =
VAR noofyears =
    COUNTROWS ( ALLSELECTED ( Sales[Year] ) )
RETURN
    CONCATENATEX (
        FILTER (
            ALLSELECTED ( Sales[Customer] ),
            VAR mytable =
                CALCULATETABLE ( VALUES ( Sales[Year] ) )
            RETURN
                COUNTROWS ( FILTER ( mytable, CALCULATE ( SUM ( Sales[Sales] ) <> 0 ) ) ) = noofyears
        ),
        [Customer],
        ","
    )
Highlighted
Super User
Super User

Re: Customers who made sales in ALL of selected years

@PBISean 

 

Please see attached file as well

 

PBISean.png

PBISean Regular Visitor
Regular Visitor

Re: Customers who made sales in ALL of selected years

Hi Zubair,

 

Thanks so much for your solution. I am still new to PBI and trying to learn. I kind of understand the logic of your solution, but not 100%. 

 

what I need the output is a matrix visual that shows:

1. repeat customer names on the column

2. the selected years on the row

3. the sales of the customer in that selected year in the value

In the screenshot below, for example, the matrix will show the A, F, K, P, U on the column, the year 2015 & 2016 on the row, and the corresponding sales in the value area. 

 

I tried hard to "tweak" your measure into the one I want but to no avail. I understand that this part:

 
FILTER (
ALLSELECTED ( Sales[Customer] ),
VAR mytable =
CALCULATETABLE ( VALUES ( Sales[Year] ) )
RETURN
COUNTROWS ( FILTER ( mytable, CALCULATE ( SUM ( Sales[Sales] ) <> 0 ) ) ) = noofyears
produces a list of customer names that meet the requirement that each of the "All Selected" customers has bigger than zero sales in every single selected years. I tried to feed this list to a calculate function but it does not work. see the file here
 
Again, really appreciate your help.
 
PBISean

 

Super User
Super User

Re: Customers who made sales in ALL of selected years

Hi
I will get back to you in few hours when i get access to my laptop.
PBISean Regular Visitor
Regular Visitor

Re: Customers who made sales in ALL of selected years

No hurry, whenever you have some time to spare. Really appreciate your help. If you are in SF Bay Area, I will take you out for a coffee. Smiley Happy

Super User
Super User

Re: Customers who made sales in ALL of selected years

Hi @PBISean 

 

Thanks Sean for the coffee offer Smiley Happy

Try this MEASURE as VISUAL level filter
Please see attached file as well

 

SalesofRepeatCustomers =
VAR noofyears =
    COUNTROWS ( ALLSELECTED ( Sales[Year] ) )
RETURN
    IF (
        SELECTEDVALUE ( Sales[Customer] )
            IN FILTER (
                ALLSELECTED ( Sales[Customer] ),
                VAR mytable =
                    CALCULATETABLE ( VALUES ( Sales[Year] ) )
                RETURN
                    COUNTROWS ( FILTER ( mytable, CALCULATE ( SUM ( Sales[Sales] ) <> 0 ) ) ) = noofyears
            ),
        1
    )

I get the following result

pbisean_.png

PBISean Regular Visitor
Regular Visitor

Re: Customers who made sales in ALL of selected years

This is BRILIANT!

PBISean Regular Visitor
Regular Visitor

Re: Customers who made sales in ALL of selected years

Hi Zubair,

 

Sorry to bother you again. Your solution definitely works and I really appreciate your help,  but I still have difficulty understanding the measure formula. I uploaded the PBI file here. These are my questions:

 

1. On the "Magic" tab: I don't understand why the measure you created (now renamed "ZubairMagic" in the uploaded fileSmiley Happy) DOESN'T even show up in the matrix. Actually it doesn't show up in any visuals on the tab. The measure in the matrix is just the sum of native column of "Sales". When I replace the sum of native column of "Sales" with a measure I created "Total Sales", I expect the result won't change, but it actually won't work. How does ZubairMagic execute this magic?

 

2. On the "Question" tab: I understand your formula "RepeatCustomerList" as follows:

This part of the formula

FILTER (ALLSELECTED ( Sales[Customer] ),
COUNTROWS ( FILTER (VALUES ( Sales[Year]), CALCULATE ( [Total Sales] <> 0 ) ) ) = [CountSelectedYears])

gives a list of Customers who

     a) have Non Zero sales, and

     b) the number of years that they have Non Zero Sales is equal to the number of years user select (since their sales is aggregated into year, this means that in each of the selected years, the customer has Non Zero sales). This is brilliant!

 

Then, with the List of Customers (LoC) "harvested" in a row, the ConcatenateX turns them into a "flat" list seperate by ",", that's what Concatenate is supposed to do.

 

However, If I use the "harvested" list of customers (LoC) and feed them into a CALCULATE function, I expect that should work. Because Calculate should take this list of value and calculate the Total Sales. But obviously it won't work as shown in the file.  Can we use a "Calculate" function to do the same? Why my approach fails?

 

I understand your time is precious. If you ignore my message here, i am completely OK. 

 

Thank you

 

PBISean

Super User
Super User

Re: Customers who made sales in ALL of selected years

Hi @PBISean 

 

I will look at the attachment and get back to you

 

Regards,

Zubair