Sales of Bikes to Married Men - CALCULATE()

A dedicated forum to help people when working through the book "Learn to Write DAX"
dimbroane
Posts: 2
Joined: Fri Aug 03, 2018 1:25 pm

Sales of Bikes to Married Men - CALCULATE()

Postby dimbroane » Fri Aug 03, 2018 1:49 pm

Hello

I suspect I am using the old book (or dataset), for my result shows $14,602,515.

Anyway, that is not the reason I am writing.

I am using the formula:

=CALCULATE(
[Total Sales Amount],
Products[Category] = "Bikes",
Customers[MaritalStatus] = "M"
)

(I would have uploaded the Data Model as a .png, but do not know how).

The result I have is interesting. I sort of understand why this happens, but am not sure.

Row Labels Sales of Bikes to Married Men
Australia $4,102,687
Canada $983,419
France $1,363,434
Germany $1,545,459
United Kingdom $1,969,411
United States $4,638,104

Grand Total $14,602,515

Using territories, the formula works just fine.
However, using Products, the formula does not:

Row Labels Sales of Bikes to Married Men
Accessories $14,602,515
Bikes $14,602,515
Clothing $14,602,515
Components $14,602,515

Grand Total $14,602,515

Why?
Is it because CALCULATE() cannot change the context evaluation (the filter context is the same as the filter used inside of CALCULATE)? As if CALCULATE would refer to itself.

By the way, how can I upload pictures?

RamanaV
Posts: 12
Joined: Thu Oct 19, 2017 12:57 pm

Re: Sales of Bikes to Married Men - CALCULATE()

Postby RamanaV » Fri Aug 03, 2018 2:56 pm

Hi,

The Measure has a CALCULATE that filters Products table for Category "Bikes" and Customers table for MaritalStatus "M".
And whatever result you got $14,602,515 is for total sales of bikes for married men.

Now coming to your two pivot tables -
The first one results are ok - because the initial filter context by Rows (in this case Territories) is retained and the values are displayed as expected.

The second one - You have put Products[Category] on Rows. This is again the initial filter context. However CALCULATE also is coming with a filter on Products[Category] namely "Bikes". So it overrides the initial filter context and displays the total sales for bikes for all the categories and hence you get same value for all the rows.

Hope this helps.

dimbroane
Posts: 2
Joined: Fri Aug 03, 2018 1:25 pm

Re: Sales of Bikes to Married Men - CALCULATE()

Postby dimbroane » Fri Aug 03, 2018 5:16 pm

Thank you - very clear answer.


Return to “Help with the Book "Learn to Write DAX"”

Who is online

Users browsing this forum: No registered users and 1 guest