Forum Discussion

mavdom18's avatar
mavdom18
Regular Visitor
3 years ago
Solved

Matrix total calculation

Hi everyone, I've been struggling to find a way to calculate the total of a matrix. I'm trying to divide the sum total of the column by 15.  Is there a way to do that?  Like in excel, we can just...
Tips and Tricks
Tutorial Requests
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi mavdom18 ,

    In Power BI, the Total row can't be customized. If you want to get the expected result, you need to create a new table contains value "Enterprise" and define its value. Here's my solution:

    1.Create a new table. Sort LOB by Index column.

    Create relationship between the two tables with LOB column.

    2.Create three measures.

    HC =
IF (
    SUM ( 'Table'[HC] ) <> BLANK (),
    SUM ( 'Table'[HC] ),
    SUMX ( ALL ( 'Table' ), 'Table'[HC] ) / 15
)

    Email% =
IF (
    MAX ( 'Table'[Email%] ) <> BLANK (),
    MAX ( 'Table'[Email%] ),
    AVERAGEX ( ALL ( 'Table' ), 'Table'[Email%] )
)

    Voice% =
IF (
    MAX ( 'Table'[Voice%] ) <> BLANK (),
    MAX ( 'Table'[Voice%] ),
    AVERAGEX ( ALL ( 'Table' ), 'Table'[Voice%] )
)

    Get the result:

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Super User
3 years ago

mavdom18 , Not very clear

 

example is

divide( calculate(sum(Table[HC]), allselected() ), 15)

mavdom18's avatar
mavdom18
Regular Visitor
3 years ago

Apologies, I just realized that the image is confusing. To give more context, enterprise is my grand total. Instead of it being just the sum of all the lob combined, I want it to be divided by 15. However, the problem is that it's not as easy as doing it in excel where I can just edit the total formula to be divided by 15.

 

  • What I wanted to do is to make a calculation in my matrix total so that it sums up all the lob and then divide it by 15.