Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Jango
Frequent Visitor

Matrix row sum as average but Values as sum

Hi All,

Jango_0-1680185534519.png

I try to create a matrix where the row total is in Average but the single values should aggregate as sum. but i can only change all aggregation to average or the row total is als as sum.

 

the matrix should look something like in the following picture. One column with row average and the values with sum aggregation

Jango_2-1680197982736.png

 


does anyone can help me with that or can give me another solution. 
Thanks!

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @Jango ,

 

Not sure if what you need is to have the total in rows or in columns has averages but you should do something similar to:

 

Matrix Values =
IF (
    ISINSCOPE ( Table[ColumnHeaders] ),
    SUM ( Table[Values] ),
    AVERAGEX (
        DISTINCT ( Table[ColumnHeader] ),
        CALCULATE ( SUM ( Table[Values] ) )
    )
)

This will give for each column the sum of the values for the total column it will give you the average of the sums.

 

This needs to be adjusted to your model but here is an example:

MFelix_0-1680204170721.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
Super User

Hi @Jango ,

 

Not sure if what you need is to have the total in rows or in columns has averages but you should do something similar to:

 

Matrix Values =
IF (
    ISINSCOPE ( Table[ColumnHeaders] ),
    SUM ( Table[Values] ),
    AVERAGEX (
        DISTINCT ( Table[ColumnHeader] ),
        CALCULATE ( SUM ( Table[Values] ) )
    )
)

This will give for each column the sum of the values for the total column it will give you the average of the sums.

 

This needs to be adjusted to your model but here is an example:

MFelix_0-1680204170721.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Jango
Frequent Visitor

exactly what i needed! Thank you!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors