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

Measures using Calculate on a table with multiple filters

So i'm having a few issues trying to work out marketing expenses by category. 

 

I have a table called "6201 Marketing Expense" and this contains the company's marketing expenses as "Debit" 

 

the table has headers as follows:

 

Date, Supplier Account, Ref, Dept, Details, T/C, Type, Debit, Credit

 

date is self explanatory, supplier account is the list of suppliers, in this example i'll use "Rightmove", "Zoopla" and "ListGlobal" as the 3 items i want to focus on. 

 

I know how to work out this formula in excel and SQL, but i'm having difficulty with the Dax statement.

 

What i want to calculate is the "Total Online Advertising cost", focusing only on Rightmove, zoopla and Listglobal entries in the table. 

 

So far i have the formula as follows:

 

Online Ad Cost = calculate(sum('6201 Marketing Expense'[Debit]), FILTER('6201 Marketing Expense', '6201 Marketing Expense'[Supplier Account] = "Rightmove"), FILTER('6201 Marketing Expense', '6201 Marketing Expense'[Supplier Account] = "Zoopla"), FILTER('6201 Marketing Expense', '6201 Marketing Expense'[Supplier Account] = "ListGlobal"))
 
the formula seems to work fine as it's accepted and no errors are shown. 
 
but i'm not getting any values in any visulisations.
 
i've checked my query and replaced "null" with "0" as i though that might be the cause. but alas, still nothing being shown. 
 
Any suggestions on how i can proceed would be a great help. 
1 ACCEPTED SOLUTION
Super User II
Super User II

I think if you write it that way it effectively does a logical AND between the filter conditions (which will eliminate all the rows).

 

You could probably simplify this down to the following:

Online Ad Cost =
CALCULATE (
    SUM ( '6201 Marketing Expense'[Debit] ),
    '6201 Marketing Expense'[Supplier Account] IN { "Rightmove", "Zoopla", "ListGlobal" }
)

 

View solution in original post

1 REPLY 1
Super User II
Super User II

I think if you write it that way it effectively does a logical AND between the filter conditions (which will eliminate all the rows).

 

You could probably simplify this down to the following:

Online Ad Cost =
CALCULATE (
    SUM ( '6201 Marketing Expense'[Debit] ),
    '6201 Marketing Expense'[Supplier Account] IN { "Rightmove", "Zoopla", "ListGlobal" }
)

 

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors