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

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,

 

Anonymous's avatar
Anonymous
Not applicable
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.