- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Average Formula

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-19-2018 11:18 AM

How can I build a DAX formula that will return the average of the highlighted column. I'm trying to do this to get the average by year & quarter:

## Re: Average Formula

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-19-2018 11:28 AM

Can you please try this

: AverageX(AllExcept(Table,Year,Quarter),(positivefeedback/Allgroups*100))

* In Place of Table keep your table name.

Thanks,

Aditya

## Re: Average Formula

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-19-2018 11:35 AM

@adityakommu I've tried doing it this way but for whatever reason it comes out to 100% for every month:

## Re: Average Formula

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-19-2018 11:37 AM - edited 06-19-2018 11:37 AM

Can you please post your calculation and few rows sample data here..

## Re: Average Formula

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-19-2018 11:47 AM

@adityakommu Sure:

And if it makes a difference, I unpivoted my original table to look like this:

So everything in the last 2 columns originally had their own column. I don't know if the unpivot throws this kind of formula off. So positive feedback would be everything that consists of a positive sounding value (strongly agree, agree, happy, very happy).

## Re: Average Formula

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-19-2018 11:49 AM

I think this should work :

avg = CALCULATE(AVERAGEx(zero,Zero[Column1]/Zero[Column2]) ,ALLEXCEPT(Zero,Zero[Quarter],Zero[Year]))

Note : Zero is table name I have created

## Re: Average Formula

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-19-2018 11:54 AM

@adityakommu Still not quite:

## Re: Average Formula

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-19-2018 12:02 PM

That's weird, It is working at my end: Can you please write 2 measure separately

Measure 1 : AVERAGEx(zero,Zero[Column1]/Zero[Column2])

Measure 2:= CALCULATE(AVERAGEx(zero,Zero[Column1]/Zero[Column2]) ,ALLEXCEPT(Zero,Zero[Quarter],Zero[Year]))

and paste your entire column list here...

## Re: Average Formula

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-19-2018 12:20 PM - edited 06-19-2018 12:22 PM

Both measures are in the middle. Could this be happening because of the way my table is setup through the unpivot? If AVERAGEX function calculates everything on a row basis, I think that may be why it's returning 100% on each row... But I'm not sure.

## Re: Average Formula

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-19-2018 12:26 PM

I do think Unpivot is causing an issue,I am surprised to what average is not calculating row-wise.

IF we fix that everything else will be done