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 i...
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

     

     

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.