cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## DAX - KEEPFILTERS not showing correct results - arbitrarily shaped set

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 ?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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
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

## 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

My goal is to understand KEEPFILTER function.

Can we solve this using KEEPFILTER ?

Highlighted
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

Thank you,
Antriksh Sharma
Highlighted
Frequent Visitor

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

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

Announcements

#### August 2020 Community Challenge: Can You Solve These?

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

#### Community Blog

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

#### 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!

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

Top Solution Authors
Top Kudoed Authors