Forum Discussion

mrothschild's avatar
mrothschild
Continued Contributor
5 years ago
Solved

Matrix sub-total calculation?

Sample Excel data: https://docs.google.com/spreadsheets/d/1o55R8MKW263heweuKcZPIvEBgyBoC5hL/edit?usp=sharing&ouid=115111367427670973715&rtpof=true&sd=true

 

The attached file has a pivot table on it, showing [% change] as the Values, [Sale Year] as columns, and [Year of Build] as rows.

 

This screenshot of a PowerBI matrix shows the average and standard deviation (i.e, "volatility") of each row of the pivot table.  

 

The [measure] to calculate the average is 

Average of Rows = 

AVERAGEX (
            SUMMARIZE (
                'Helivalues Transaction History',
                'Helivalues Transaction History'[Sale Year],
                'Helivalues Transaction History'[Year of Build]
            ),
            [Average Annualized Price change (weighted by Model Year Units)]
        )

 

The standard deviation of the rows = 

Standard Deviation of Rows =
    STDEVX.S (
                SUMMARIZE (
                    'Helivalues Transaction History',
                    'Helivalues Transaction History'[Sale Year],
                    'Helivalues Transaction History'[Year of Build]
                ),
                [Average Annualized Price change (weighted by Model Year Units)]
            )

 

I'm trying to create a new Matrix, as one part of a larger calculation, where the Standard Deviation of the above rows is represented in each of the locations shown below.   

 

 

Questions:

1) In the red-shaded row, you'll see a bunch of NaN.  How do I get that row, which is a Matrix sub-total, to produce the average of the data lying above it, and still retain the data above.  I'm okay if this is generated in a completely different [measure] that creates a new column of identical data. 

 

Need Help
  • mrothschild's avatar
    mrothschild
    5 years ago

    Final PBIX File here: https://drive.google.com/file/d/12_k_tO8BdrXV73Lbho_whabGP-jOlasP/view?usp=sharing

     

    Code to create the weighted average measure:

    WAVG Measure = 

VAR DESIRED_ROWS = 
    FILTER(
        'Table',
        'Table'[Column1] > 0      &&
        'Table'[Attribute (from a PowerQuery unpivot] = "attribute1 selected"  ||
        'Table'[Attribute (from a PowerQuery unpivot] = "attribute2 selected"  ||
        'Table'[Attribute (from a PowerQuery unpivot] = "attribute3 selected"  ||
    )

RETURN
    DIVIDE(
            SUMX (
                DESIRED_ROWS,
                'Table'[Denominator Column] * 'Table'[Value]
            ),
            SUMX (
                DESIRED_ROWS,
                'Table'[Denominator Column]
            )
    )

     

    Code to generate Matrix values from a weighted average measure:

    _A = 

// Note - below one can use any of the *X DAX functions, such as SUMX, AVERAGEX, MEDIANX, STDEVX.S, etc.
       
VAR OUTPUT =     
            AVERAGEX(
                SUMMARIZE (
                        ALLSELECTED('Table'),
                        'Table'[Row Column]
                    )
                , [WAVG Measure]
            )
        
RETURN
    OUTPUT

     

    Code to summarize the Matrix value [measure]

    _A Summarized = 

// Choose AVERAGEX or SUMX below depending on one's needs


VAR OUTPUT_CELL = 
    AVERAGEX(
      SUMMARIZE('Table','Table'[Matrix Row Column])
        , [WAVG measure]
    )
RETURN
    OUTPUT_CELL

     

     

17 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    When you create measures that you plan to use in a matrix visual you need to consider that the matrix visual does FOUR computations:

     

    - individual cell

    - column total

    - row total

    - grand total

     

    Things like "SELECTEDVALUE()" have no meaning in the totals. You cannot use that. The totals most often need to use iterator functions like AVERAGEX  to make sense.  When you run into these issues often enough it is worth reminding yourself of the guidance: "Think like the Grand Total".  Very often a measure that works for a Grand Total will also work for the other totals and for the individual cells.  The other way round - not so much.

     

    Can you formulate what should happen in each of the four scenarios?

    • mrothschild's avatar
      mrothschild
      Continued Contributor
      5 years ago

      lbendlin 

       

      Thank you so much for your response and super fantastic simple-to-understand explanation.  I've had a hard time distinguishing between [column] (as opposed to [measure] or [table] and "column" as in of a pivtotable. 

       

      In this case, I'm trying to produce a measure that provides the average of the pivottable column for each Year of Sale where errors/NaNs are treated as BLANK(). That measure should also sub-total to the average of the rows when across rows, and should be the average of each of the individual cells as a Grand Total.   

       

      In the past few days, I've figured out that this formula works:

       

      Average of pivoted column

VAR OUTPUT = 
        AVERAGEX( 
            ADDCOLUMNS (
                SUMMARIZE (
                    ALLSELECTED ('Table'[Table column]),
                    'Table'[Table column],
                    "SUM", SUM ( 'Table'[Table row] )
                ),
                "Percent", CALCULATE ( SUM ( 'Table'[Table row] ) )  
            ),
            [measure of the pivot column value]
)
RETURN
    OUTPUT

       

      But in some of my pivottable columns, where I use IFERROR({calc}, BLANK() ) the above-formula returns blank in all instances.  

       

       

      • lbendlin's avatar
        lbendlin
        Super User
        5 years ago

        Is your error caused by a division by zero?  Try to already apply the BLANK() substitution there.