Forum Discussion
Custom correlations or visuals
I'm no statistician but, following the sample, I think you're right:
- Unpivot the values for each hour (i.e. highlight Name and Date, then Unpivot Other Columns),
- Rename the values columns to reflect the variable - e.g. "Number of errors", "Total proc time", "Volume of messages"; then
- Merge the three tables on Name and Date and Time Band with your three variables then on each row, unless you want to build table relationships etc..
Then add a corrplot visual via R per the sample. Note that as of yesterday's update, you can alternately add that specific corrplot as a custom "R-powered visual" via https://app.powerbi.com/visuals/ So far though, I've found no way to customise the plot with that custom visual, rather than writing it in R.
My quick test seemed to work per below screenshot, though all the zeros for Errors didn't seem to help. As I say, I'm no statistician - good luck...
This sample Correlation Plot may be of use: https://community.powerbi.com/t5/R-Script-Showcase/Correlation-Plot/td-p/58462
Thanks Steve for a Great suggestion . Somehow I feel my data is not primed for this type of analysis. Do I have to unpivot my data an then do the analysis. I wanted to get the format of the raw data correct (pivot vs unpivot).
I am struggling with getting my dataset in right structure for chart & analysis
I'm no statistician but, following the sample, I think you're right:
- Unpivot the values for each hour (i.e. highlight Name and Date, then Unpivot Other Columns),
- Rename the values columns to reflect the variable - e.g. "Number of errors", "Total proc time", "Volume of messages"; then
- Merge the three tables on Name and Date and Time Band with your three variables then on each row, unless you want to build table relationships etc..
Then add a corrplot visual via R per the sample. Note that as of yesterday's update, you can alternately add that specific corrplot as a custom "R-powered visual" via https://app.powerbi.com/visuals/ So far though, I've found no way to customise the plot with that custom visual, rather than writing it in R.
My quick test seemed to work per below screenshot, though all the zeros for Errors didn't seem to help. As I say, I'm no statistician - good luck...
Brilliant Steve:
Let me try this out and see if it works. The chart is exactly what I am looking for. Truly appreciate your insights.
Aj
Hello Steve:
I am trying to merge per your post
Merge the three tables on Name and Date and Time Band with your three variables then on each row, unless you want to build table relationships etc..
I unpivot the columns. I used merge query and append query but that is not working. Data is not appending correctly. I wanted to append columns not rows. Any suggestion. Should I just copy and paste.
If you've unpivoted each table, you should have 3 x tables with Name, Date and Attribute as common columns and the variables you are correlating as the last column in each, per sample below for "Number of errors":
Your new query to merge those on the common columns should look something like:
let Source = Table.NestedJoin(#"Volume of messages",{"Name", "Date", "Attribute"},#"Number of errors",{"Name", "Date", "Attribute"},"NewColumn",JoinKind.Inner), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Volume of messages", type number}}), #"Expanded NewColumn" = Table.ExpandTableColumn(#"Changed Type", "NewColumn", {"Number of errors"}, {"Number of errors"}), #"Merged Queries" = Table.NestedJoin(#"Expanded NewColumn",{"Name", "Date", "Attribute"},#"Total Proc Time for all messages per hour",{"Name", "Date", "Attribute"},"NewColumn",JoinKind.Inner), #"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Total proc time"}, {"Total proc time"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded NewColumn1",{{"Number of errors", "Number of errors"}, {"Total proc time", "Total proc time"}, {"Volume of messages", "Volume of messages"}}) in #"Renamed Columns"If you click the settings "cog" beside the first Source "Applied Step" at the right, you can see the join settings I used. I hope that works for you.