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
Anonymous
Not applicable

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
Anonymous
Not applicable

Can you please try this

 

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

 

* In Place of Table keep your table name.

 

Thanks,

Aditya

Anonymous
Not applicable

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

1.PNG

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

@Anonymous Still not quite:

1.PNG

Anonymous
Not applicable

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

Anonymous
Not applicable

@Anonymous

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.

Anonymous
Not applicable

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

Anonymous
Not applicable

@Anonymous Gotcha. I will see if I can recreate everything without unpivoting. Thank you for all your help!

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.