Mulitple filters when calculating SUM with DAX

DAX DAX Calculations
1

Hi,

I’m trying to create a new measure ‘SalesWest’ in Power BI Desktop but I can’t make it work. I’m working with the table below:

Table 1 / factSales 
----------------------------- 
id | storeid | quantity | amount 
----------------------------- 
1 |   1  |    1      |  100 
2 |   1  |    1      |  100 
3 |   3  |    2      |  200  
4 |   4  |    3      |  300  
5 |   4  |    3      |  300  
6 |   4  |    6      |  600 

Table 2 / dimStores 
----------------------------- 
storeid | name  
-----------------------------    
1  |    west       
2  |    north          
3  |    east        
4  |    south

I would like to create a measure that sums all sales where the Store is West and the Sales Quantity is not equal to 2.

I tried to use the calculated function, but when I tried to insert two filters, I got an error that says: "The value cannot for “…” cannot be determined. Either “…” doesn’t exist, or there is not a current row for a column named “…”.

What function should I use then?

2

You are going to need to review the CALCULATE function. Here is a link to Sam’s resources that will help you along.

Thanks
Enterprise%20DNA%20Expert%20-%20Small

3

Ok this is relatively straight forward here.

You just need to master a few fundamentals in Power BI and DAX and you’ll be all set.

Examples below

West Sales 1 =
CALCULATE (   SUM ( factSales[amount] ), 
       FILTER ( factSales, factSales[storeid] = 1 && factSales[quantity] <> 2 ))

You could use this one

West Sales 2 = 
CALCULATE (    SUM ( factSales[amount] ), 
       FILTER ( dimStores, dimStores[name] = "West" ), 
              FILTER ( factSales, factSales[quantity] <> 2 ))

I would work through this course module here as soon as you can.

Once you can master concepts like ‘context’ and functions like CALCULATE and FILTER, you’ll have no problem answering these questions in the future.

Thanks
Sam