Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Problem with CALCULATE / FILTER

Hi I'm trying to produce a table from some simple Sales data in the following format:     I'd like to produce a table showing Customer, Number of Sales and "Number of Sales to Major Custome...
  • Anonymous's avatar
    Anonymous
    7 years ago

    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]       
)

  • Anonymous's avatar
    Anonymous
    7 years ago

    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. 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Try:

Total Purchases >50 = 
CALCULATE(
    [Number of Sales],
    FILTER(
        Customers,
        [Number of Sales] >50
    )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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: 

     

    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:

     

    If I change my Measure to 

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

    I get: 

     

    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!