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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.