Forum Discussion

Nankaina's avatar
Nankaina
Helper I
4 months ago
Solved

Matrix Row Totals within Row Values

I'm recreating a matrix from Excel into Power BI, and attempting to match the formatting as much as possible. There's a fairly simple matrix in it, with values something like this:

I would like to display these values as a fraction of the row total, updating with new data as it comes in. ie:

I've managed to get the inverse, where it shows the values as a fraction of the column total, by using the following DAX:

Table Output = COUNT('Sample Data'[X-values]) & "/" & CALCULATE(COUNTA('Sample Data'[X-values]), ALLSELECTED('Sample Data'[Y-values]))

Leading to this close, but incorrect, table: 

But when I swap X-values and Y-values in the CALCULATE, I don't get the desired row totals; I get each value just divided by itself (10/10, 9/9, 22/22, etc.)

 

How can I get these to display with the sum of the row as their denominator, instead of the sum of the column?

Need Help
  • parry2k's avatar
    parry2k
    4 months ago

    Nankaina add following two measures:, and use display measure in the matrix visual

     

    Sum Measure = SUM ( YourTable[YourColumn] )

Display Measure = 
FORMAT ( [Sum Measure], "#" ) & "/" &
FORMAT ( CALCULATE ( [Sum Measure], ALLSELECTED ( YourTable[X-ValueColumn] ) ), "#" )

     

     

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    4 months ago

    Nankaina add following two measures:, and use display measure in the matrix visual

     

    Sum Measure = SUM ( YourTable[YourColumn] )

Display Measure = 
FORMAT ( [Sum Measure], "#" ) & "/" &
FORMAT ( CALCULATE ( [Sum Measure], ALLSELECTED ( YourTable[X-ValueColumn] ) ), "#" )

     

     

  • pcoley's avatar
    pcoley
    Super User
    4 months ago

    As Nankaina just answered, you should adjust the meassure called Table Output taking into account all the [X-values] and not the [Y-values] in the ALLSELECTED formula.
    ALLSELECTED('Sample Data'[X-values])

    Table Output CORRECT= COUNT('Sample Data'[X-values]) & "/" & CALCULATE(COUNTA('Sample Data'[X-values]), ALLSELECTED('Sample Data'[X-values])

## Actual Meassure (wrong):
## Table Output = COUNT('Sample Data'[X-values]) & "/" & CALCULATE(COUNTA('Sample Data'[X-values]), ALLSELECTED('Sample Data'[Y-values])