cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
freshwave Frequent Visitor
Frequent Visitor

Possible to Average Matrix Column Total Instead of Sum

I have a matrix table that is a measure performing an averagex on a dataset.  Resulting number and the total on the bottom of the matrix is calculating correctly.

The matrix column total seems to only perform in a summary fashion, and I was wondering if there is a trick to get it to actually give me the average for the row.

Any help would be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
freshwave Frequent Visitor
Frequent Visitor

Re: Possible to Average Matrix Column Total Instead of Sum

Someone on another website was able to help me.  All I had to do was modify my formula to divide it by the distinctcount of places, and it gives me the number I want in the column total without changing all of the other values.

 

Hope this is able to help someone in the future.

 

OLD FORMULA

Open Incidents - Average =
     AVERAGEX(
          'DATE',
          CALCULATE (
               COUNTROWS ( 'Open Incidents' )
          )
)

 

NEW FORMULA

Open Incidents - Average - NEW =
     DIVIDE(
          AVERAGEX(
               'DATE',
               CALCULATE (
                    COUNTROWS ( 'Open Incidents' )
               )
          ),
          DISTINCTCOUNT( 'Open Incidents'[PLACE] ),
     0
)

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Possible to Average Matrix Column Total Instead of Sum

Hi @freshwave ,

 

Could you please share your sample data and excepted result to me if you don't have any Confidential Information.Please upload your files to One Drive and share the link here.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
freshwave Frequent Visitor
Frequent Visitor

Re: Possible to Average Matrix Column Total Instead of Sum

Hi Frank,

 

I was not able to share my file due to confidentiality and size reasons.  I mocked up a very simple PBIX file that shows my issue.  Hopefully you can give me some guidance.

 

https://1drv.ms/u/s!AnwFbg8GYO4JhASaL2bhyoYgbCro

 

Thanks,

Jeff

Highlighted
freshwave Frequent Visitor
Frequent Visitor

Re: Possible to Average Matrix Column Total Instead of Sum

Someone on another website was able to help me.  All I had to do was modify my formula to divide it by the distinctcount of places, and it gives me the number I want in the column total without changing all of the other values.

 

Hope this is able to help someone in the future.

 

OLD FORMULA

Open Incidents - Average =
     AVERAGEX(
          'DATE',
          CALCULATE (
               COUNTROWS ( 'Open Incidents' )
          )
)

 

NEW FORMULA

Open Incidents - Average - NEW =
     DIVIDE(
          AVERAGEX(
               'DATE',
               CALCULATE (
                    COUNTROWS ( 'Open Incidents' )
               )
          ),
          DISTINCTCOUNT( 'Open Incidents'[PLACE] ),
     0
)

View solution in original post

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: 524 members 4,354 guests
Please welcome our newest community members: