"Which Segment Buys more?" Lesson - One measure works but not the other

[Nangys]

Hi All,

I'm trying to find a solution for the "Which Segment Buys more?" Lesson of the PowerPivot training.

I took a different approach an created a Calculated Column in the Customer table:

= IF( AND([Kids] = "Yes" , [Marital Status] <> "Married") , "Yes" , "No")

Then I create a simple "Sum" measure:

SUM(fctSales[SaleAmount])

and I get the correct result. The measure is correctly filtered by the "context filters".

The problem is when I try to find the number of single parents in each location. I created this measure

=DISTINCTCOUNT(Customers[Cust ID])

This gives the correct number at the gender level, but ignores the row context of the city.

I'm not sure why one works and the other one work "partially".

Regards,

Fernando

 

[Chihiro]

Try,

CALCULATE(DISTINCTCOUNT(Customers[Cust ID]), "Column Name")

Where Column Name is the name of column where your filter operation is applied.

 

[Nangys]

Hi Chihiro,

It didn't work. I got the same result.

I tried:

CALCULATE(DISTINCTCOUNT(Customers[Cust ID]) , Stores[State])

Regards,

Fernando

 

[Nangys]

Hi All,

The problem was that I was using the DISTINCTCOUNT on the Customer table (which is a Lookup/Dimensional table), and not on the fctSales table (fact table).

It seems that it couldn't apply the Location filter (row context) because we don't have that field in the Customer table (?).

However, it gave the correct overall number for the gender because it was able to apply that filter in that table since the table contains the gender column (?).

Regards,

Fernando

 

[Chihiro]

Ah, thought you had both columns in same table.

Normally, what I'd do in this instance is to create joined table in Access. Build your query there and then apply final measure in Excel side.