Forum Discussion

mrothschild's avatar
mrothschild
Icon for Continued Contributor rankContinued Contributor
5 years ago

Matrix sub-totals from a quick measure?

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

____________________________________________

**UPDATE** 

 

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.

 

The row-subtotals in this matrix directly below in the update section are not the average of their associated columns.

 

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

 

 

 

 

 

_______________________________________________

Tab in question is called "Troubleshooting for forum help"

 

 

Desired results:

I need a measure that calculates the simple average of the columns in the upper Matrix.  I've tried a bunch of different ways but unfortunately, I'm only familiar with DAX via brute force methods of copying what others on the forums have suggested and don't have an intuitive understanding myself.  

 

The intended output is for the upper Matrix to have row subtotals that are the columnar average. 

 
200820092010201120122013201420152016201720182020
$5,476k$5,604k$5,735k$5,537k$5,683k$5,793k$5,906k$5,808k$5,930k$6,056k$6,184k$6,452k

 

 

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi mrothschild 

    You Upside(Graph) measure is built based on [Expected (Graph)] and [______G1 - Coeff_Upside {equals} sqrt{power{{E},{F}}}] measures. So I think wrong subtotal in column is caused by logic in these two measures.

    Your calculated logic and data model is complex, I suggest you to share a easy sample without any sensitive data with us. This may make it easier for us to understand your requirements.

    Here I will give some suggestions:

    1. It is better for you to rebuild [Expected (Graph)] and [______G1 - Coeff_Upside {equals} sqrt{power{{E},{F}}}].

    2. You can try to calcualte the average you want directly in fault result in if function.

    =
 IF(
        HASONEVALUE('Helivalues Transaction History'[Year of Build]),
         [Expected (Graph)] * [______G1 - Coeff_Upside {equals} sqrt{power{{E},{F}}}]
        , ...//Code calculate total average directly.
    )

    Best Regards,
    Rico Zhou

     

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