DAX - KEEPFILTERS not showing correct results - arbitrarily shaped set

[snph1777]

I am doing some training work in AdventureWorksDW database.

I have two measures:   

Sum of SalesAmount = SUM(FactInternetSales[SalesAmount])

MonthlyAverageSales = AVERAGEX (
                                                            VALUES(DimDate[EnglishMonthName]),
                                                            [Sum of SalesAmount]
                                                          )

I have developed the above report, choosing only two years 2006 and 2007, and only 4 months using slicers.

While the measure [Sum of SalesAmount] shows the correct value, the other measure [MonthlyAverageSales] shows incorrect data in the Total row; however, both measures are right, when aggregated for each year 2006 and 2007.

The measure [MonthlyAverageSales] at the Total row should actually be $ 5,916,696.73/8, which is $ 739,587.09.  (8 in denominator represents 8 months - Jan,Feb,Nov,Dec for each year - 2006 and 2007)

Instead, what I see is $ 5,916,696.73/4, which is $1,479,174.18; (4 in denominator represents 4 months-  Jan,Feb,Nov,Dec, combining both years together)

Now, I have changed the formula, and have created a new measure:

MonthlyAverageCorrect = AVERAGEX (
                                                                KEEPFILTERS(VALUES(DimDate[EnglishMonthName])),
                                                                [Sum of SalesAmount]
                                                              )

I still do not see any change. Instead of getting $ 739,587.09, I still see $1,479,174.18.

Where am I in error ?

[harshnathani]

Hi @snph1777 ,

Not sure if this is monthly average. 

I think you just need the total divide by the no. of months.

You can use this measure.

Avg SUMMARIZE = 

var a = DISTINCTCOUNT(DimDate[YearMonthValue])

RETURN
[Sum of SalesAmount]/a

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[snph1777]

Thanks

[AntrikshSharma]

Create a new column in date table that has month and year in same column example: January 2020 and then inside the measure use this column.

MonthlyAverageCorrect =
AVERAGEX ( VALUES ( DimDate[Year Month] ), [Sum of SalesAmount] )

[snph1777]

Thanks for the answers.

My goal is to understand KEEPFILTER function.

Can we solve this using KEEPFILTER ?

[AntrikshSharma]

Can you post the pbix file? Because the measure with KEEPFILTERS is correct.

[snph1777]

I am not able to post the file, since I am a new member.

But all the measures and the data model relationships are shown as pictures.

[AntrikshSharma]

Upload the file to dropbox, google drive and share the link for the file.

[snph1777]

https://drive.google.com/file/d/1mXeWjF0dw33KL4Vg-q3h8l3jGqB2oVJX/view?usp=sharing

[AntrikshSharma · Accepted solution]

You could try this:

Avg =
AVERAGEX (
    KEEPFILTERS (
        FILTER (
            ALL ( DimDate[EnglishMonthName], DimDate[CalendarYear] ),
            [Sum of SalesAmount] > 0
        )
    ),
    [Sum of SalesAmount]
)

or

Avg CROSSFILTER = 
AVERAGEX (
    CROSSJOIN (
        DISTINCT ( DimDate[EnglishMonthName] ),
        DISTINCT ( DimDate[CalendarYear] )
    ),
    [Sum of SalesAmount]
)

or 

Avg SUMMARIZE=
AVERAGEX (
    SUMMARIZE ( DimDate, DimDate[EnglishMonthName], DimDate[CalendarYear] ),
    [Sum of SalesAmount]
)

[snph1777]

Ok thanks; any reason, why my formula did not work ? I have imported an Excel Power Pivot workbook into Power BI

[AntrikshSharma]

Because when context transition happens it creates a filter for each month and then overwrites existing filter over months but the years remain untouched and let's say the first value from VALUES () is January due to context transition CALCULATE overwrites filters over months and the new filter is with January, January is calculated both for 2007 & 2008, so either you use

Average Sales = 
AVERAGEX( 
    VALUES( 'Date'[Calendar Year Month]) , 
    [Total Sales]
) 

or the options above, creating a column in Date table is more efficient than KEEPFILTERS 

[MattAllington]

Your Averagex iterates over the 4 months,  not the 8 unique combinations of year and month. If you want to iterate over the instances of the months, you need a measure that will do that. You could try replacing values(cal[month name]) with summarize(sales,cal[month name],cal[year])

[snph1777]

Thanks