CALCULATE Context

Anonymous — Thu, 09 May 2019 15:52:12 GMT

Hi community,

I have a problem with understanding why a formula in DAX works. It is a question around CALCULATE and about how to change the FILTER CONTEXT. I got it to work at the end but I cannot understand why it is working. The Power BI file is on my GitHub and I share the link.

The Domain

I have one table in Power BI that contains the following information:

museum_visitors( Date, Month, Year, Museum, Visitors )

Data are monthly, so I have 1 entry for each month from 2015 to 2018. I am trying to calculate a seasonality index in this way:

Average Visit by month / Average Visit overall

I want to display the seasonality index in a table that contains the month name. I place a slicer with the names of the different museums in my report.

Then I create the measure Avg Visitors := AVERAGE( museum_visitors[Visitors] )

This measure, when placed in a table with the Month correctly gives me the average of the different months.

Now I have some problem with the overall average. In fact, since the measure needs to work in this table, I understand that I need to use CALCULATE to get rid of the month name that is present in the filter context and calculates the overall average so I write this measure:

Avg Overall := CALCULATE(
                                   AVERAGE(    museum_visitors[Visitors] ),
                               ALL( museum_visitors[Date] )
                         )

I would expect that when I drag the measure in the table I obtain one column with equal values that is the overall average but I have the very same result of Avg Visitors. I tried to change the second condition in this way ALL( museum_visitors[Month] ) but it did not work and I also tried to make explicit the second condition writing:

Avg Overall := CALCULATE(
                                    AVERAGE( museum_visitors[Visitors] ),
                                    FILTER(
                                           museum_visitors,
                                           ALL( museum_visitors[Date] )
                                    )
                          )

The above resulted in an error. Why?

I achieved this doing the following:

Seasonality Index = AverageVisitors_CurrentMonth / OverallAverage
AverageVisitors_Jan = (Visitors_Jan2015 + Visitors_Jan2016 + Visitors_Jan2017) / 3

The way I solved my problem

Finall I solved my problem using:

SeasonalityIndex := [AverageVisitors] / [Avg Overall]

Avg Overall := CALCULATE(
                                AVERAGE( museum_visitors[Visitors] ),
                                ALLSELECTED()
                       )

My question
I don't understand why the formula above works. Could someone explain this to me?

Link to my Git Hub where is the file

For reference my Power BI file is on my GitHub:
https://github.com/ellenne/PowerBI/tree/master/10.%20Out

Re: CALCULATE Context

parry2k — Fri, 10 May 2019 05:08:36 GMT

Anonymous gone thru your post and seem like in the end you are asking why ALLSELECTED works, correct?

Let me ask you why you think it is working? Did you read about ALLSELECTED function here?

topic CALCULATE Context in DAX Commands and Tips

CALCULATE Context

Re: CALCULATE Context