cancel
Showing results for
Did you mean:
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

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" }
)``````

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" }
)``````

Announcements

#### Happy New Year from Power BI

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