CALCULATE based on multiple tables

[Yamabushi]

Hi,

I'm lost with understanding a certain case.

I have the following measure:

Total Sales Large Customers in 2004 on Non-Working Days =

CALCULATE(

[Total Sales],

FILTER(

ALL(Customers),

[Total Sales] > 10000

),

FILTER(

ALL('Calendar'),

'Calendar'[CalendarYear] = 2004 &&

('Calendar'[DayNumberOfWeek] = 6 ||

'Calendar'[DayNumberOfWeek] = 7)

)

)

[TotalSales] is the classic SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]) measure.

So if I understand my own measure correctly, I'm calculating the TotalSales of the customers who bought more than 10.000$ worth of items, but I'm calculating only the sales I made to those customers in 2004 on Saturdays and Sundays, right?

When I put just this measure on a visual, I think I'm getting the correct result. But when I put more fields on the visual (for example CalendarYear), everything just goes blank (see screenshot).

Would someone be so kind to explain what exactly is going on here and why I can't put a field I'm calculating by in the measure into the visual and still have it show results. The visual with just the measure looks like this:

Any help would be greatly appreciated!

[v-rzhou-msft]

Hi @Yamabushi 

I have a test on your measure. Your measure logic is to filter the cutomer whose [Total Sales] > 10000 and return the sum of [Total Sales] on calendar year =2004 and day on Saturday or Sunday.

I know you have three tables [Calendar],[Customers] and [Sales]. Make sure you have created relationships between three tables.

My code filter the cutomer whose [Total Sales] > 10000 and return the sum of [Total Sales] on calendar year =2021 and day on Saturday or Sunday.

 [Total Sales] for Customer A =9717<10000,

 [Total Sales] for Customer B =169657>10000,

So my measure should return B's sum of  [Total Sales] on Saturday or Sunday in 2021.

Total Sales Large Customers in 2021 on Non-Working Days = 
CALCULATE(
[Total Sales],
FILTER(
ALL(Customers),
[Total Sales] > 10000
),
FILTER(
ALL('Calendar'),
'Calendar'[CalendarYear] = 2021 &&
('Calendar'[DayNumberOfWeek] = 6 ||
'Calendar'[DayNumberOfWeek] = 7)
)
)

Result is as below.

Maybe there is no customer with [Total Sales] >10000, you can add +0 behind your measure, if it return 0, this means there is no result return by your measure. 

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulOlding]

When you put CalendarYear in your visual it'll affect this part of your measure

FILTER(
ALL(Customers),
[Total Sales] > 10000
)

it'll change it from...

customers with total sales over 10,000 for all time

to...

customers with total sales over 10,000 in the current evaluated CalendarYear

[amitchandak]

@Yamabushi , Try a measure like

var _sale = calculate([Total Sales], allexcept(Customers,Customers[Customers]))
return
CALCULATE(

Sumx( FILTER(
values(Customers[Customers]),
_sale > 10000
),[Total Sales])
FILTER(
ALL('Calendar'),
'Calendar'[CalendarYear] = 2004 &&
('Calendar'[DayNumberOfWeek] = 6 ||
'Calendar'[DayNumberOfWeek] = 7)
)
)