Calculate Measure gets different results depending on filter

[frodriguezw]

Hi,

I'm new to this forum, so I really appreciate your help here!

I downloaded Sales and Marketing Sample PBIX which contains VanArsdel Sales. I have the following table:

This is the Product Table and I have a Conditional Calculated Column names isVanArsdel in which it says Yes if the product is from VanArsdel as Manufacterer and No otherwise.

This column is connected to SalesFact Table through ProductID, which is unique ID in ProductTable (so each Product will have only one manufacturer).

If I try to calculate VanArsdel Unit Sales for a specific year, I created 2 different measurements:

Measure 1:

CALCULATE([Total Units], FILTER(ALL('Product'[isVanArsdel]), 'Product'[isVanArsdel]="Yes"))

Measure 2:

CALCULATE([Total Units], FILTER(ALL('Product'[Manufacturer]), 'Product'[Manufacturer]="VanArsdel"))

[Total Units] is just SUM(SalesFact[Units])

So far so good. If I look at the Multi-row card I get the same number (297,606). The problem is when I start using this measures in other calculated measures. For some reason, measurement 2 is giving numbers to all of manufacturers, not just VanArsdel as I had expected, so if then I want to measure VanArsdel Market share for example, and select a different manufacturer with a slicer, instead of getting 0%  in VanArsdel's market share I get values using measurement 2, but this doesn't happens when I use measurement 1.

So to look for differences, I created a matrix in which I looked at the manufacturer and both measurements. There I found the difference:

What I find is odd is that in both measurements, FILTER should apply to the exact same rows, so I don't get why I get different results.

Can someone help me in trying to explain this?

If it can help to understand this confusion, if I put both variables together with the isVanArsdel as column in a matrix I obtain the following matrix:

Again, everything is fine until the totals, so when I filter by Manufacturer, the second measure will still show as values all of VanArsdel totals.

I appreciate any help!

Greetings,

[Anonymous]

I need to calculate the sum of VCI_RECEIVED_Head on group by feedyard ,pen_id and lot_id .can you help me here . even if the rationid is different but for example in below row2 and row3 are unique on mentioned above 3 columns so in the calcuation only 123 should be considered . total result should be =118+123+129+166+250 .Hope you got it

 

[frodriguezw]

Hi! I just looked at this.

I don't know if is the best answer, but it worked for me!

Create column

KEY_ID =[FEEDYARD]&[PEN_ID]&[LOT_ID]

(This will give you the unique key for the parameters you are looking for)

Then create Measure DISTINCT_AVERAGE

DISTINCT_AVERAGE:=AVERAGEX(SUMMARIZE('Table',[KEY_ID],[VCI_RECEIVED_Head]), [VCI_RECEIVED_Head])

(This will average the amounts for the key you are looking for)

Finally create the DISTINCT_SUM measure:

DISTINCT_SUM:=SUMX(DISTINCT('Table'[KEY_ID]),[DISTINCT_AVERAGE])

You will get the following table:

Hope this help you!

Likewise, if you don't want to create another column, this will also work:

DISTINCT_AVERAGE = AVERAGEX(SUMMARIZE('Table',[FEEDYARD],[LOT_ID],[PEN_ID],[VCI_RECEIVED_Head]), [VCI_RECEIVED_Head])

DISTINCT_SUM = SUMX(SUMMARIZE('Table',[FEEDYARD],[LOT_ID],[PEN_ID],[VCI_RECEIVED_Head]),[DISTINCT_AVERAGE])

It will give the same results.

Hope this could help! I'm new at DAX so maybe there is a better way of doing this.

Greetings,

[Arentir · Accepted solution]

Hi @frodriguezw

Welcome to the wonderful world of DAX!

It is an interesting one. What you see is all due to filter context.

I won't go into the detail of evaluation context, if you are not too familiar with this I strongly suggest that you look into the ressources I am putting at the bottom of this post.

Calculate is a function that modify the filter context. In your matrix, your filter context is Manufacturer, ie each row should compute the total unit of Abbas, Aliqui, etc...

But calculate changes the rule of the game and discards your natural filter context:

Measure 1: You have a filter context on Manufacturer (table row), but your calculate will only computes when IsVanArsdel = "Yes". Which is true only for Manufacturer = VanArsdel. Conclusion: you have no value but for this row

Measure 2: You have a filter context on Manufacturer (table row), but your calculate does too as this as you have specify this very column ("ALL('Product')[Manufacturer])). Therefore it will ignore your table row completely and give you the result of your condition for any manufacturer on rows. Conclusion: your table row is ignored and the value for VanArsdel is repeated for each row.

For more on Evaluation context ( Filter Context + Row Context):

A true master of DAX and great introduction

https://www.youtube.com/watch?v=klQAZLr5vxA (from 01:15:00)

https://www.youtube.com/watch?v=6ncHnWMEdic&t=2403s (the whole thing)

https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/

[frodriguezw]

Thank you very much for clarifying this!

I will have to take extra careful in the filters I'm applying!

Greetings,