How to show AVG value in Matrix visual instead of Row Total.

[Anonymous]

Hi Everyone,

I want to show Average instead of row total in matrix visual. Please help me with this.

FYI, Please find the below attached snap for my requirement.

Link for sample data: 
https://docs.google.com/spreadsheets/d/1ywvspnfl7O2DscU7HLRzDPMHd0iYAPwU/edit#gid=188546882

Thanks in Advance.

@amitchandak @PaulDBrown @Vvelarde @MarcelBeug @Greg_Deckler @johnt75 

[Anonymous · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see the attachment), please check if that is what you want. You can create a measure as below to replace the original one...

Measure = 
VAR _seldis =
    SELECTEDVALUE ( 'Table'[Discipline] )
VAR _sellevel =
    SELECTEDVALUE ( 'Table'[Level] )
VAR _sum =
    SUM ( 'Table'[Availability] )
VAR _ssum =
    CALCULATE (
        SUM ( 'Table'[Availability] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Discipline] = _seldis
                && 'Table'[Level] = _sellevel
        )
    )
VAR _scount =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Week] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Discipline] = _seldis
                && 'Table'[Level] = _sellevel
        )
    )
VAR _avg =
    DIVIDE ( _ssum, _scount )
RETURN
    IF ( ISINSCOPE ( 'Table'[Week] ), _sum, _avg )

In addition, you can refer the following links to get it.

ISINSCOPE – DAX Guide

Use IsInScope to get the right hierarchy level in DAX

If the above ones still not help you get the expected result, please provide more raw data in the table (exclude sensitive data) with Text format and your expected result with backend logic and special examples.It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Anonymous]

Thanks for your solution.

[pratyashasamal]

Hi @Anonymous ,
You can create a measure and add it to matrix.

Then measure:

Measure =

var _average = calculate(AVERAGE('Table'[values]),REMOVEFILTERS('Months column'))

return

IF(SELECTEDVALUE('Months column'[month])="average",_average,SUM('Table'[values]))
Then you can remove the total column by going to marix properties->General->Row Totals on/off .

Thanks ,
Pratyasha Samal 
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

[Anonymous]

Hi @pratyashasamal ,

Thanks for your solution. I have to create a measure by taking the refference of your measure, its working. 

But, it gives average in column total, but my requirement is columns should be sum and i want to show row total as average. 

FYI, please find the above attachment, the red highlited total column should be average, and remaining same as sum.

[pratyashasamal]

Hi @Anonymous ,
Try this type of measure once ,

Measure = IF(ISINSCOPE('Table'[category]),SUM('Table'[count]),AVERAGE('Table'[count]))

Thanks ,
Pratyasha Samal 
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

[Anonymous]

HI @pratyashasamal ,

I tried the same, even it gives the same result.

[pratyashasamal]

Hi @Anonymous ,
Please check if this link is helpful for you : https://stackoverflow.com/questions/64869614/how-to-show-avg-value-in-matrix-widget-along-with-total-in-power-bi

https://stackoverflow.com/questions/66658350/power-bi-add-total-average-column-in-matrix

Thanks ,
Pratyasha Samal

[pratyashasamal]

Hi @Anonymous ,
Hope you were able to resolve the issue ?
Thanks ,
Pratyasha Samal