Forum Discussion
Helper IIIMatrix row totals are incorrect
I have been trying to show some measure of cross-correlation between a criticality factor and a performance rating. I was hoping to use weighted averages, but the weighted average function in PBI does not seem to work (at least, not for my data).
So, I have a set of data from a survey of several respondents, regarding features of a product and how those features perform in several different models of the product. The respondents have varying degrees of familiarity with the different models, so most have only entered performance ratings in 1 or 2 products (3 tops). I have created a column that cross-mulitplies the performance rating (0-10) times the criticality score (1-5).
Because some models have been used by more people, I want to use the average cross-multiplied score (to control for the number of respondents using a particular model).
So, I put together a matrix with features in rows and product models in columns, and this is what I got (snip):
The Column "totals" in the top table are averages (because I guess they have to be?), and are the correct averages of all the values in the column. The row "totals," however, are not correct. This is the excel version of the same table:
I am at a loss to understand why the numbers are incorrect.
I think I have it figured out. PBI is averaging the actual values, not the averages of the values for the products. I think that's actually a better way to do it.
12 Replies
- Greg_Deckler
Community Champion
ripstaur This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
ripstaurSo, I will admit that I am a newbie, and although I have been through the free online training on filter context twice now, I am still lost with all the filter and -X functions.
So, my understanding is that when I built a matrix, and used the Features as rows and the Products as columns, I was pretty much grouping the results into a cross-tab with those two categories, so I get the sum or average of all the values in the table for each Feature by Product. I don't have (or at least, have not consciously or deliberately created) any measures at all. I just have fields that I have selected for the matrix columns, rows and values, and I have plugged "Cross Multiplied" in for Values.
So, I don't know where I would begin to add the HASONEFILTER function to anything, or why it would be useful. The only filters on my table are the default (All) filters that PBI puts in when you select the fields...
I have tried a number of iterations in Excel to see if I can replicate the numbers PBI is giving me for row totals (actually row averages), but I haven't been able to find an algorithm that gives me the PBI results.
- Greg_Deckler
ripstaur I may have misunderstood the question. What is not correct? The last row of the matrix or the last two columns?
- Greg_Deckler
ripstaur Go into Power Query Editor. Create a New Blank Query. Click on Advanced Editor. Ctrl-A to select all the code. Copy in all the code that I provided replacing everything that is currently in the blank query. Save the query.
- ripstaur
I think I have it figured out. PBI is averaging the actual values, not the averages of the values for the products. I think that's actually a better way to do it.
