cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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.
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
)