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 

 

Some progress here.  New PBIX file: https://drive.google.com/file/d/1pG3ua4BOWyIJhhh4dOwG9ac8UPWqUsYo/view?usp=sharing

This is a before screenshot

 

 

 

By changing the first above STDVX [measure] to the following by adding ALLNOBLANKROW first thing after summarize

 

 

VAR DEPRECIATION_VOL_OBSERVED =
        
            STDEVX.S(
                SUMMARIZE (
                        ALLNOBLANKROW('Helivalues Transaction History'),
                        'Helivalues Transaction History'[Sale Year]
                    )
                , [Average Annualized Price change (weighted by Model Year Units)]
            )

 

 

 

I get **a** sub-total rather than NaN.  What that subtotal represents isn't entirely apparent, and isn't dynamic across the pivoted columns.  It's summarized as 5.76% for each column and the grand total, rather than the average of what I perceive to be the available data points.

 

 

 

The intended output is for the {A01} pivot tabled-column to produce the average of the pivot table column immediate to its left.  Based on what's shown {A01} should = 5.38% for 2016, 5.73% for 2017, 11.13% for 2018, 6.14% for 2019, and 6.14% for 2020.

lbendlin's avatar
lbendlin
Super User
5 years ago

"produce the average of the pivot table column immediate to its left"

 

Your measure has no idea what that means. "right" and "left" are meaningless unless you can help with an index column, or a guaranteed sort order.

  • mrothschild's avatar
    mrothschild
    Continued Contributor
    5 years ago

    lbendlin 

     

    apologies.  My language was colloquial and imprecise.  For A01, I want the average of A for each of 2016, 2017, 2018, etc.  Visually on the Matrix it's physically oriented to the left, but I want it associated with sale year.  

    • aj1973's avatar
      aj1973
      Community Champion
      5 years ago

      Hi mrothschild 

      If I may get involved in this, did you verify all the multitude of measures you have in your model??

      Because I have seen many wrong doing like here, did you mean SUM or COUNT because it doesn't make sens  to sum up the years!!!

       

      here is the difference

       

      also in your page only one slicer interacts with your Pivotal tabel

       

      Date Table if it is not going to used why is it in the model!!

      Sorry Pal, your model and your demande is not making it any clear to be solved. 

      • 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)
    )