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

How can I build DAX that will give me the average of the highlighted measure? I need to get this to a point where I can see the average by any year or quarter I need to see it by:

 

1.PNG
 

 

NOTE: This data is coming from a table that has unpivoted columns. Everything I've tried so far only returns a average of 100% for each row and I'm assuming the unpivot factor is why it cannot compute correctly.

4 REPLIES 4
Phil_Seamark
Employee
Employee

HI @Anonymous

 

Sounds like a job for the AVERAGEX function.  Something along the lines of...

 

New Measure = AVERAGEX ( 'table'[fieldname] , [Positive %] )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

Hi @Phil_Seamark,

 

Same thing happening unfortunately:

1.PNG

Hi @Anonymous

 

What number would you like to see in the top row of the highlighted  [Measure] column?  An average of the Year or Quarter?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

@Phil_Seamark So the percentages you see is a count of how much positive feedback there was for that month/year. I'm getting those percentages through a formula that looks like this:

1.PNG

 

This is how that data looks in my query editor:

1.PNG 

 

So everything in the last two columns are unpivoted columns. Here is how the data looked in its original state:

1.PNG 

 

The original requirement was to get counts and percentages of everything considered a "positive", "negative" or "neutral" value. I was not sure how to count these values across columns, so that's why I took the unpivot route.

 

Now the next requirement is to get a trailing 12 month average of the "positive" % over time. Then using those trailing 12 month percentages, creating a measure that averages those %'s for the previous quarter. I was told by someone else the AVERAGEX function may not work in conjunction with an unpivoted table, but I'm not sure if that's true or not...

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.