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

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 Super Contributor
Super Contributor

Re: Average

HI @mrainey

 

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!

mrainey Member
Member

Re: Average

Hi @Phil_Seamark,

 

Same thing happening unfortunately:

1.PNG

Phil_Seamark Super Contributor
Super Contributor

Re: Average

Hi @mrainey

 

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!

mrainey Member
Member

Re: Average

@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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 153 members 2,100 guests
Please welcome our newest community members: