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

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

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
)

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 125 members 1,646 guests