Forum Discussion

ripstaur's avatar
ripstaur
Icon for Helper III rankHelper III
5 years ago
Solved

Matrix 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 doe...
Need Help
  • ripstaur's avatar
    ripstaur
    5 years ago

    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. 

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
5 years ago

ripstaur I may have misunderstood the question. What is not correct? The last row of the matrix or the last two columns?

ripstaur's avatar
ripstaur
Icon for Helper III rankHelper III
5 years ago

It is the totals in the last column - the matrix puts them there, and I want them, but I need them to be correct. Since all the numbers in the rows and columns are averages, PBI forces me to use averages in the (actually sub-) totals in the right-most column and bottom row. The numbers in the totals row at the bottom are correct (each is an average of the numbers in the column). THe numbers in the right-hand column are not the averages across the rows - I don't know how they are calculated. They are somewhat close, but not correct. 

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    ripstaur Can you share the PBIX or sample source data to recreate? Not sure where that number is coming from otherwise.

    • ripstaur's avatar
      ripstaur
      Icon for Helper III rankHelper III
      5 years ago

      I'd be happy to...I actually spent an hour or so putting an example file together, but I don't see an attachment link here. 

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        5 years ago

        ripstaur - Try this:

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdRda8IwFAbgv3LolUKt6UnbVe/GgkzYhkwZgor4EV0gGknSsf77pXYfyjoY7blILs553uQmmc081p/PJ/xwmmeEUL4V2lv4Mw9elMwOHMQRtlq8cWAgDNwzNr1qj7kWKwlP2WHNdTExddUplvNYOcuE5hurdA5qB1/H/fQJdjHtIsEQgKR9pDB6BFfnC23Y8Lm8GXxXUJNVO/x0FxX2KKyMEcaujnYpjm6XkutlgQhi6naMaSDVvjKR/E50DdgJyUPowJ065dDCdmDfbX1PG/qooY8b+qShv2no04a+V8/XQViNkn70BxpMx7ejIePrbP+g9gMX8e+EmPgYJjB51cpaycdWnQKTm/IhX1VIoUhumfYljkgP1rnlVQKw+ArOwL0fP01iHyn6NI1KAjvNubdYfAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column", type text}}),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type",2),
    #"Removed Top Rows" = Table.Skip(#"Removed Bottom Rows",8),
    #"Split Column by Position" = Table.SplitColumn(#"Removed Top Rows", "Column", Splitter.SplitTextByPositions({0, 32}, false), {"D:\Temp>dir.1", "D:\Temp>dir.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"D:\Temp>dir.1", type datetime}, {"D:\Temp>dir.2", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"D:\Temp>dir.2", Text.Trim, type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Trimmed Text", "D:\Temp>dir.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"D:\Temp>dir.2.1", "D:\Temp>dir.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"D:\Temp>dir.2.1", Int64.Type}, {"D:\Temp>dir.2.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"D:\Temp>dir.1", "Date"}, {"D:\Temp>dir.2.1", "Size"}, {"D:\Temp>dir.2.2", "Filename"}})
in
    #"Renamed Columns"