Forum Discussion

mrothschild's avatar
mrothschild
Continued Contributor
5 years ago

Matrix sub-total rows not seemingly numerically associated with underlying data

PBIX FILE: https://drive.google.com/file/d/1j_4CrUj0fPh0xK6hnO7OZ-uIWkoP5Kdg/view?usp=sharing

________________________________________________

***UPDATE***

Identified one of the offending measures:

 

When OBSERVED_DEPRECIATION is the Matrix output, and it's programmed as follows: 

 

VAR OBSERVED_DEPRECIATION = 
        SUMX(
            SUMMARIZE (
                'Helivalues Transaction History',
                'Helivalues Transaction History'[Sale Year]
            ),
            [Average Annualized Price change (weighted by Model Year Units)]
        )

 

 

The Matrix output looks like this (since I changed from AVERAGEX to SUMX, I would expect the Subtotals to accumulate: 

 

When OBSERVED_DEPRECIATION is the Matrix output, and it's programmed as follows: 

 

 

VAR OBSERVED_DEPRECIATION = 
        AVERAGEX(
            SUMMARIZE (
                'Helivalues Transaction History',
                'Helivalues Transaction History'[Sale Year]
            ),
            [Average Annualized Price change (weighted by Model Year Units)]
        )

 

 

When I change back to AVERAGEX(), the subtotals don't change. 

 

 

 

___________________________________________________________________

PROBLEM:  The values in the sub-total of the Matrix visual don't seem to be numerically associated with the underlying data that is supposed to be feeding them as highlighted in the red row below.

 

Intended output is ideally, the individual rows for the "Upside (Graph)" columns remain as shown, and the sub-total represents the average of the rows above it.  Alternatively, I could create a calculated measure where the "Upside (purposefully for graphing)" column represents the average of all rows in the column directly to its left.  

 

I'm trying to create a line chart from the data in the above Matrix.

 

The line on the line chart should be the average of the columns of "Upside (Graph) in the matrix.  So the data point for 2019 should be (roughly from eyeballing) around $15,000,000.  For reasons that are completely foreign to me, the datapoint on the line chart is pulling the $5,504,716 for the 2019 observation.

 

Even when I slice my PowerBI to individual rows associated with the Matrix, the row-subtotal changes slightly from what's shown above, but still isn't an average of a single datapoint = single datapoint.

 

 

The Measure to create the values for the Matrix table is relatively complex with VARs but the final calculation is:

 

 

 

 

 

 

MATRIX_OUTPUT = 

            (EARLIEST_OBSERVED_PRICE * (ABS(SIGN(SELECTED_DEPRECIATION)) * POWER((1 + SELECTED_DEPRECIATION),t))) 
            *   COEFF_UPSIDE 

 

 

 

 

 

 

EARLIEST_OBSERVED_PRICE = 

 

 

 

 

 

EARLIEST_OBSERVED_PRICE = 
    CALCULATE ( 
    [Average Unit Price Current Year (weighted by Model Year Units)], 
    'Helivalues Transaction History'[Sale Year] = EARLIEST_SALE_YEAR 
    ) 

 

 

 

 

 

 

EARLIEST_SALE_YEAR = 

 

 

 

 

 

EARLIEST_SALE_YEAR = 
        CALCULATE(
            MIN('Helivalues Transaction History'[Sale Year]),
            ALLSELECTED('Helivalues Transaction History'[Sale Year]) 
        )

 

 

 

 

 

 

SELECTED_DEPRECIATION = 

 

 

 

 

SELECTED_DEPRECIATION = 

    DIVIDE(
    CALCULATE(
        AVERAGE('Input - Depreciation'[Input - Depreciation]),
        ALLSELECTED('Input - Depreciation'[Input - Depreciation])
           
    ),
    100
    )

 

 

 

 

 

t = 

 

 

 

t = 
        CURRENT_SALE_YEAR - EARLIEST_SALE_YEAR

 

 

 

 

note, I thought/think this could be an issue, with the AVERAGE() not being the correct function to pull in what I'd want, but even if I used SELECTEDVALUE() the same output results

CURRENT_SALE_YEAR = 

 

 

 

CURRENT_SALE_YEAR = 
    AVERAGE('Helivalues Transaction History'[Sale Year])

 

 

 

 

COEFF_UPSIDE = 

 

 

 

COEFF_UPSIDE = 
    POWER(  ABS( SQRT( ABS (1 + SELECTED_DEPRECIATION + 2 * VOLATILITY) ) )   ,   t   )

 

 

 

 

VOLATILITY = 

 

 

 

VOLATILITY = 
    ROW_STANDARD_DEVIATION 
        * POWER(
                (1 + 
                    DIVIDE( ABS(SELECTED_DEPRECIATION - OBSERVED_DEPRECIATION) , 
                        ABS(OBSERVED_DEPRECIATION)
                    )
                ) , 
            2)

 

 

 

 

ROW_STANDARD_DEVIATION = 

 

 

 

ROW_STANDARD_DEVIATION = 
        STDEVX.P(
            SUMMARIZE (
                'Helivalues Transaction History',
                'Helivalues Transaction History'[Sale Year]
            ),
            [Average Annualized Price change (weighted by Model Year Units)]
        )

 

 

 

 

OBSERVED_DEPRECIATION = 

 

 

 

OBSERVED_DEPRECIATION = 
         AVERAGEX(
            SUMMARIZE (
                'Helivalues Transaction History',
                'Helivalues Transaction History'[Sale Year]
            ),
            [Average Annualized Price change (weighted by Model Year Units)]
        )

 

 

 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi mrothschild ,

     

    Sorry, I did not particularly understand your question. What is your expected result? Your data is too large for me as a stranger. Can you simplify the problem, give a simple sample data and expected results, I will help you as much as possible.

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • mrothschild's avatar
      mrothschild
      Continued Contributor

      Anonymous 

       

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

       

      The tab in question is "Troubleshooting for forum help".  The intent is to have the cells/values in the bottom matrix equal the columnar average of the above Matrix.  As shown in the screenshot below, the "Aggregated Model" slicer located on the left hand of the dashboard in the middle (from top to bottom) has selected only "EC-135".

       

       

      The current programming I'm attempting to use that generates the [measure] for the lower Matrix is: 

      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