Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
snph1777
Helper V
Helper V

DAX - KEEPFILTERS not showing correct results - arbitrarily shaped set

I am doing some training work in AdventureWorksDW database.

 

pbi1.GIF

 

I have two measures:   

 

Sum of SalesAmount = SUM(FactInternetSales[SalesAmount])

 

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

 

pbi2.GIF

 

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]
                                                              )

 

pbi3.GIF

 

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 ?

 

1 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]
)

View solution in original post

13 REPLIES 13
harshnathani
Community Champion
Community Champion

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!

Thanks

AntrikshSharma
Community Champion
Community Champion

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] )

 

Thanks for the answers.

 

My goal is to understand KEEPFILTER function.

 

Can we solve this using KEEPFILTER ?

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

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.

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

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]
)

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

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 

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])



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors