Why does my filter not work with calculated measures in Power BI, Power Pivot Excel?

2
votes

I'm working with AdventureWorksDW2014. I created 2 measures, however they did not behave the way expected when i applied filter.

I have 2 tables FactInternetSales and DimCustomer. They are linked through CustomerKey. I have 2 calculated measures. One measure (All Gender) sums up the Freight.

All Gender Freight = CALCULATE( sum(InternetSale[FactInternetSales.Freight]))

The other measure sums up the Freight of only Male

Male Measure Freight =
CALCULATE (
    SUM ( InternetSale[FactInternetSales.Freight] ),
    Customer[DimCustomer.Gender] = "M"
)

Now, if i use Power BI or Power Pivot Excel to view first Measure for all gender. I see this. Which is what i expected.

Gender  All Gender

  F      370341.79

  M    363627.8142

enter image description here

For second measure, what i cannot explain is that i expect the total Freight for Female to be zero, since i did not include that in my Measure.

Gender  All Gender

  F    363627.8142 --Should be 0

  M    363627.8142

enter image description here

1
ssaspowerpivotpowerbidax

1 Answers

2
votes

You were missing FILTER function: 

Male Measure Freight =
CALCULATE (
    SUM ( InternetSale[FactInternetSales.Freight] ),
    FILTER(Customer,Customer[DimCustomer.Gender] = "M")
)