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

aj1973 

 

Thank you for that.  I can confirm that "YOUR TABLE" in the PBI file your shared is the intended outcome.  That calculation is a version of SUMPRODUCT in Excel, where it's delivering the weighted-average price based on units in a given year.   

 

Imagine it was sneakers and we were selling some for $50 and some for $100.  If we sold twice as many $50 sneakers our "Total Sales" would be the same for each sneaker type, but the weighted average price would be $67, not $75.  At an individual row level, you would be always, in your example be dividing by Y and left with X, but at a summarized aggregated level, the math changes.

 

Sneaker PriceUnits SoldRevenuesPrice per Sneaker sold
$5010$500$50
$1005$500$100
    
SUBTOTAL15$1000$67
aj1973's avatar
aj1973
Community Champion
5 years ago

Sorry but I think you didn't get my point. To fix an issue you need to go step by step with your measures and test them one by one until you get to that issue.

For instance the measure "Upside (Graph)" is full of variables and none of them makes sense to me therfore how do you know that the values in the Matrix are accurate and if so what makes the total coming out wrong???

 

Please re read the remarks in the file I sent you and follow the actions i made to get to the confirmation that "MY TABLE" was close to your inteded income.

 

Just to add, it is good to use Variables but it's a must that you know what those variables are going to do and how to use them because sometimes Var don't work as intended when it is embeded inside CALCULATE(as an example). So adding simple and small measures and then combine them into one can be more accurate that using multiple VAR inside a single measure. 

 

Good luck

  • mrothschild's avatar
    mrothschild
    Continued Contributor
    5 years ago

    aj1973 

     

    I think I've found the solution.  Will post here when done, but please stop looking at this for now.

     

  • mrothschild's avatar
    mrothschild
    Continued Contributor
    5 years ago

    aj1973 

     

     

    Unless the calculation results in something different for your file than for the one you shared with me, "YOUR TABLE" is producing the desired output, and "MY TABEL" is not.  

     

    But you have peeled back to identify the core problem, which is the methods I've been reading about that are used to summarize a [measure] calculated using a simple average don't seem to be working for [measures] calculated using a weighted average approach.  

     

    The following matrix In this file: https://drive.google.com/open?id=12_k_tO8BdrXV73Lbho_whabGP-jOlasP

     

     

    is my attempt at a [measure] by [measure] approach.  The _TEST columns are based on simple averages and not weighted averages and the methodology to get to the desired row sub-totals is working.  

     

    I need to figure out how to do the same for a weighted average [measure].

  • mrothschild's avatar
    mrothschild
    Continued Contributor
    5 years ago

    aj1973 

     

    Premature, but making progress.

     

     

    The new interim step measure, {A int1}  goes onto the matrix in individual cells as [Units] * [{A}] and is calculating correctly at an individual cell level.  The [Units] is calculating correctly at both individual and row-subtotal level. 

     

    So now trying to summarize {A int1}, consistently associated with the individual cell data that is going into it.

     

  • mrothschild's avatar
    mrothschild
    Continued Contributor
    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