Forum Discussion
Helper IIForecasting and Correlation between categories dependent on 2 value columns
Hi All,
Thanks in advanced for any suggestions. I am attempting to build two models off of my large dataset hosted in SQL Server that will give me a better understanding of correlations between categories, as well as potentially using the same model for predictive modeling.
The data is formatted in the below way, with 2 value fields that make up the # of items, and the revenue from those items. There is also a mix of binary fields that indicate if they completed the action, as well as non-binary fields that describe what category they fall into.
Most of the correlation models I have seen thus far have the data formatted where each column, apart from 1, is assigned a value, then they calculate the correlation between the values. I am trying to use the non-value fields AS by which to provide a correlation.
To give an example using the below data set, i am trying to see if the combinations of the "used x" binary fields and the category fields are associated with positive effect on the revenue or not. E.G. which categories and service combination provide the best revenue.
| Name | StartDate | Category | Category 2 | Used Service 1 | Used Service 2 | Used Service 3 | Used Service 4 | Used Service 5 | Month of Transaction | Year of Transaction | # of units shipped | revenue from units |
| John | 1/1/2021 | Small Business | Food Services | 1 | 1 | 0 | 0 | 1 | 1 | 2021 | 2 | $100 |
| John | 1/1/2021 | Large Business | Apparel | 0 | 1 | 1 | 1 | 1 | 2 | 2022 | 2 | $200 |
Hi Sut_Datanaut,
You can duplicate the source query table to create a new query table and use unpivoted columns feature on the shipment indicator fields.
Unpivot columns - Power Query | Microsoft Learn
Then you can use the 'company name', 'year', 'month' fields as matric row, 'enabled shipment indicator'(unpivoted shipment values) , 'net revenue' as matrix value to create a matrix visual for data analysis.
After these steps, you can compare the same company different month records who has changes on the 'shipment indicator' service units and correspond company 'net revenue' changes. (If the company enabled or disable more shipment services, the corresponding month 'net revenue' improve/reduce to show the relationship between two field values)
In addition, you can also try to use date field as x-axis, the 'shipment service' and' net revenue' as y-axis to create a line chart to show these relationship more clearly.(these graphs can be filtered by 'company name’)
Regards,
Xiaoxin Sheng
4 Replies
Hi Sut_Datanaut,
Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Sut_DatanautHi Xiaoxin,
The sample PBI file is on my google dive here: https://drive.google.com/file/d/1mAwmtcs3Hq2ETrJXM8w16mpV4VUpnasV/view?usp=sharing
To give you an example of a pre-concieved notion I would like to prove using a correlation measure. The values "Volume of Shipments" and "Net Revenue" are the value fields for the each account ID's total volume and revenue for each month. The volume and revenue fields are split up by the binary fields, as well as fields haivng to do with the package (service level). There are also other fields that will be the same for each user, so their user's segment (small business, medium business, large business), is the same for all packages under a user, becuase it is a user segment.
I am trying to see if either a shipment or user segment is positively or negatively correlated to producing more volume of shipments or revenue.
Thanks,
Hi Sut_Datanaut,
You can duplicate the source query table to create a new query table and use unpivoted columns feature on the shipment indicator fields.
Unpivot columns - Power Query | Microsoft Learn
Then you can use the 'company name', 'year', 'month' fields as matric row, 'enabled shipment indicator'(unpivoted shipment values) , 'net revenue' as matrix value to create a matrix visual for data analysis.
After these steps, you can compare the same company different month records who has changes on the 'shipment indicator' service units and correspond company 'net revenue' changes. (If the company enabled or disable more shipment services, the corresponding month 'net revenue' improve/reduce to show the relationship between two field values)
In addition, you can also try to use date field as x-axis, the 'shipment service' and' net revenue' as y-axis to create a line chart to show these relationship more clearly.(these graphs can be filtered by 'company name’)
Regards,
Xiaoxin Sheng