cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

Accepted Solutions
Highlighted
Impactful Individual
Impactful Individual

Re: DAX - KEEPFILTER not showing correct results - arbitrarily shaped set

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

Thank you,
Antriksh Sharma

View solution in original post

13 REPLIES 13
Highlighted

Re: DAX - KEEPFILTER not showing correct results - arbitrarily shaped set

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 a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Highlighted
Impactful Individual
Impactful Individual

Re: DAX - KEEPFILTER not showing correct results - arbitrarily shaped set

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

 


Thank you,
Antriksh Sharma
Highlighted
Frequent Visitor

Re: DAX - KEEPFILTER not showing correct results - arbitrarily shaped set

Thanks for the answers.

 

My goal is to understand KEEPFILTER function.

 

Can we solve this using KEEPFILTER ?

Highlighted
Impactful Individual
Impactful Individual

Re: DAX - KEEPFILTER not showing correct results - arbitrarily shaped set

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

Thank you,
Antriksh Sharma
Highlighted
Frequent Visitor

Re: DAX - KEEPFILTER not showing correct results - arbitrarily shaped set

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.

Highlighted
Impactful Individual
Impactful Individual

Re: DAX - KEEPFILTER not showing correct results - arbitrarily shaped set

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

Thank you,
Antriksh Sharma
Highlighted
Frequent Visitor

Re: DAX - KEEPFILTER not showing correct results - arbitrarily shaped set

Highlighted
Impactful Individual
Impactful Individual

Re: DAX - KEEPFILTER not showing correct results - arbitrarily shaped set

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

Thank you,
Antriksh Sharma

View solution in original post

Highlighted
Community Champion
Community Champion

Re: DAX - KEEPFILTERS not showing correct results - arbitrarily shaped set

Hi @ksenthile ,

 

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!

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.