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 lbendlin 

 

Here is a cleaned up version of the PBIX: https://drive.google.com/file/d/12_k_tO8BdrXV73Lbho_whabGP-jOlasP/view?usp=sharing

 

Here is the current formula that's not generating the intended output:

 

 

Upside (averaged within a given sale year for graph output) = 

VAR OUTPUT = 
        AVERAGEX( 
            ADDCOLUMNS (
                SUMMARIZE (
                    ALLSELECTED ('Helivalues Transaction History'[Sale Year]),
                    'Helivalues Transaction History'[Sale Year],
                    "COUNT", COUNT ( 'Helivalues Transaction History'[Year of Build] )
                ),
                "__Output", [Upside (Graph)]  
            ),
            [__Output]
)


RETURN
    
    OUTPUT

 

 

Here is the current output:

 

 

 

The intended output is that the Matrix at the bottom of the above-screenshot is populated with the columnar average of the Matrix at the top of the above-screenshot.

 

The formula for the [measure] in the top Matrix is: 

 

Upside (Graph) = 

VAR EXPECTED_PRICE = 
    
    IF(
        HASONEVALUE('Helivalues Transaction History'[Year of Build])
        , [Expected (Graph)]
        , AVERAGEX('Helivalues Transaction History',[Expected (Graph)])
    )

VAR COEFF_UPSIDE = 
   
    IF(
        HASONEVALUE('Helivalues Transaction History'[Year of Build])
        , [______G1 - Coeff_Upside {equals} sqrt{power{{E},{F}}}]
        , AVERAGEX('Helivalues Transaction History',[______G1 - Coeff_Upside {equals} sqrt{power{{E},{F}}}])
    )

VAR UPSIDE_GRAPH =
    EXPECTED_PRICE * COEFF_UPSIDE


VAR OUTPUT_FINAL = 
        AVERAGEX( 
            ADDCOLUMNS (
                SUMMARIZE (
                    ALLSELECTED ('Helivalues Transaction History'[Year of Build]),
                    'Helivalues Transaction History'[Year of Build],
                    "SUM", COUNT ( 'Helivalues Transaction History'[Sale Year] )
                ),
                "__Output", UPSIDE_GRAPH 
            ),
            [__Output]
)
 

RETURN

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

 

 

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!