cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrainey Member
Member

Average Formula

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:

1.PNG

10 REPLIES 10
adityakommu Member
Member

Re: Average Formula

Can you please try this

 

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

 

* In Place of Table keep your table name.

 

Thanks,

Aditya

mrainey Member
Member

Re: Average Formula

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

1.PNG

adityakommu Member
Member

Re: Average Formula

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

mrainey Member
Member

Re: Average Formula

@adityakommu Sure:

1.PNG 

 

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

1.PNG 

 

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

adityakommu Member
Member

Re: Average Formula

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

mrainey Member
Member

Re: Average Formula

@adityakommu Still not quite:

1.PNG

adityakommu Member
Member

Re: Average Formula

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

mrainey Member
Member

Re: Average Formula

@adityakommu

1.PNG 

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.

adityakommu Member
Member

Re: Average Formula

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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)