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

Problem with CALCULATE / FILTER

Hi

I'm trying to produce a table from some simple Sales data in the following format:

 

TIF1.PNG

 

I'd like to produce a table showing Customer, Number of Sales and "Number of Sales to Major Customers", where "Major Customer" is one who's made more than 50 purchases.  

Currently, only one company (MegaMart)has made more than 50 purchases:

DAXFILTER2.PNG

. but I don't want to hard-doe a filter on Mega MArt, as, in the real world, other companies would beceome "Major Customers" with a few more sales.

 

So:  I created some measures, including one using a filter on count of sales :

 

Number of Sales = COUNTROWS(Sales)

Sales to Major Customers = CALCULATE([Number of Sales],FILTER(Sales,[Number of Sales]>50))

.. with another "Quick & Dirty" measure to check the numbers are correct

CheckCount = CALCULATE([Number of Sales],Sales[Customer]="MegaMart Inc")

 

But I haven't got the result I want:

 

DAXFILTER1.PNG

This suggests that my "Sales to Major Customers" measure is incorrect, but I can't see why. Coudl anyone help?

 

PBI is at https://1drv.ms/u/s!AmxJyApgEAcYgtU39dbp7CqqPbIK7g

 

Thanks!

 

 

 

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Nick_M New Contributor
New Contributor

Re: Problem with CALCULATE / FILTER

see if this is what you had in mind:

Sales to Major Customers = 
SUMX (
       CALCULATETABLE(
           Sales,
            FILTER(
                ADDCOLUMNS(
                    SUMMARIZE(
                        Sales,
                        Customers[Customer]
                    ),
               "Sales by Customer", 
                CALCULATE( 
                    COUNTROWS( Sales), 
                        FILTER( 
                            ALL ( Sales), 
                            Sales[Customer] = EARLIER(Customers[Customer]))
                )
                ),
           [Sales by Customer] > 50
            )
       ),
       [NumberofSales]       
)

Final Matrix.png

Nick_M New Contributor
New Contributor

Re: Problem with CALCULATE / FILTER

It can look overly complex, but if you take it bit-by-bit it's really not that  bad. Just remember to work from the inside out. summarize and addcolumns are just a way to make a virtual table, and then I added a column to that table and used that to filter sales since filters are tables. But if this is for an intro type course, you would be better served trying for a simplie example. 

7 REPLIES 7
Nick_M New Contributor
New Contributor

Re: Problem with CALCULATE / FILTER

Try:

Total Purchases >50 = 
CALCULATE(
    [Number of Sales],
    FILTER(
        Customers,
        [Number of Sales] >50
    )
)
oldhasbeen Regular Visitor
Regular Visitor

Re: Problem with CALCULATE / FILTER

Nick

 

Thanksfor the suggestion but it doesn't work. BUT I think I have a clue to the problem:

 

If I change my Measure to 

Sales to Major Customers = CALCULATE([NumberofSales], FILTER(Customers,[NumberofSales]>20))

I get: 

DAXFF7.PNG

 

I think the significance of the 27 is that there are > 20 Carrot sales for MegaMart, but no other sales > 10 for any other customer/product combo:

 

Matrix27.PNG

If I change my Measure to 

Sales to Major Customers = CALCULATE([NumberofSales], FILTER(Customers,[NumberofSales]>10))

I get: 

GT10.PNG

 

So, what I think I need to do is to change my measuree so it counts ALL sales for each customer, ignoring the "Product" context. I'm not sure how to do this - maybe use ALL? But how?

 

Any suggestions?

 

Thanks!  

Nick_M New Contributor
New Contributor

Re: Problem with CALCULATE / FILTER

can you upload some sample data?  Onedrive works well

oldhasbeen Regular Visitor
Regular Visitor

Re: Problem with CALCULATE / FILTER

Highlighted
Nick_M New Contributor
New Contributor

Re: Problem with CALCULATE / FILTER

see if this is what you had in mind:

Sales to Major Customers = 
SUMX (
       CALCULATETABLE(
           Sales,
            FILTER(
                ADDCOLUMNS(
                    SUMMARIZE(
                        Sales,
                        Customers[Customer]
                    ),
               "Sales by Customer", 
                CALCULATE( 
                    COUNTROWS( Sales), 
                        FILTER( 
                            ALL ( Sales), 
                            Sales[Customer] = EARLIER(Customers[Customer]))
                )
                ),
           [Sales by Customer] > 50
            )
       ),
       [NumberofSales]       
)

Final Matrix.png

oldhasbeen Regular Visitor
Regular Visitor

Re: Problem with CALCULATE / FILTER

Nick, 

 

Thanks for this, your solution works

 

For me, however, it's too complex - I'm a trainer devising some Power BI/DAX training, and I'm struggling to understand how your solution works (I've never used ADDCOLUMN, SUMMARIZE or EARLIER) , so this is probably too advanced an example for a 2 or 3 day day course.  I'll devise something simpler,

 

Nick_M New Contributor
New Contributor

Re: Problem with CALCULATE / FILTER

It can look overly complex, but if you take it bit-by-bit it's really not that  bad. Just remember to work from the inside out. summarize and addcolumns are just a way to make a virtual table, and then I added a column to that table and used that to filter sales since filters are tables. But if this is for an intro type course, you would be better served trying for a simplie example.