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

     

     

aj1973's avatar
aj1973
Community Champion
5 years ago

Hi mrothschild 

Before fixing the second Matrix, are you sure the values in the first Matrix are accurate?

same here

 

mrothschild's avatar
mrothschild
Continued Contributor
5 years ago

aj1973 lbendlin 

 

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

Excel file with exported results from upper Matrix: https://docs.google.com/spreadsheets/d/12nChVgYinw5RMB-M5S4lv96hLQQzMwjT/edit?usp=sharing&ouid=115111367427670973715&rtpof=true&sd=true

____________________________________________

I've added a new tab, called "Troubleshooting for forum help - buildup. . . "

This indicates that the upper matrix in the original screenshot below is all "fruit from a poisonous tree" at a summarized level but not at an individual cell level, which I believe is what you were driving at aj1973 .

 

The data in each of the individual cells is accurate/desired/intended.  The summarized columnar average of these antecedent measures is wrong. 

 

The programming for each of these [measures] is all structured the same, so I'm hoping if I can fix this structure everything else will fall into place.  {A} uses STDEVX.S, but STDEVX.P also doesn't summarize to the desired output.  {B} & {C} use AVERAGEX, and {C} isn't summarizing at all, i.e., BLANK() in one of the sub-total rows, even though two individual cells are above it, and is completely off when being summarized elsewhere - a bunch of negative numbers being summarized to a positive number.

 

 

{D}, {E}, and {G} are all functions of {A}, {B}, and {C}, so unless the first 3 summarize correctly, I imagine will be hard for these to summarize correctly.

 

{F} is summarizing correctly.

 

 

{A, B, C, etc.} =

VAR TARGET_MEASURE=
            AVERAGEX(
                SUMMARIZE (
                        ALLNOBLANKROW('Helivalues Transaction History'),
                        'Helivalues Transaction History'[Sale Year]

                    )
                , [Average Annualized Price change (weighted by Model Year Units)]
            )
        

RETURN
        IF(
            HASONEVALUE('Helivalues Transaction History'[Year of Build])
            , TARGET_MEASURE , 
            AVERAGEX('Helivalues Transaction History', TARGET_MEASURE)
        )

 

 

More broadly, I apologize for my inability to articulate the issues clearly and the idiosyncracies in the coding.  I'm relatively new to DAX and don't have an intuitive understanding.  Most of what I've built is equivalent to duct tape and chicken wire that come from hitting a wall, going to the forums, and trial & error until I find something that works.

 

Thanks for your help!