Forum Discussion
How to create line graph from calculated columns?
Hi all,
I am working with a dataset which includes the Metric and the date which each metric was raised. I have tabulated the data on Power BI as per below:
Where "Current Month", "Previous Month" and "Previous 2 Month" is a calculated column which returns a '1' value depending on the month which the individual metric was raised; the above table sums up the 1 values together. The "Current vs Previous Difference" and "Previous vs Previous 2 Difference" are also calculated columns that subtracts "Current Month" to "Previous Month" or "Previous Month" to "Previous 2 Month."
My goal is to create the below table from the two 'difference' columns:
My question is how should this be achieved? I'm struggling to figure out what to use for the X-axis and Y-axis; I suspect I need to format the data in a certain way before it can be graphed? The mockup graph above was just me manually creating an Excel table as per below but I'm not sure how to replicate this manual table on Power BI and have the columns and values automatically determined.
Hi dcheng029
If I understood correctly, in order to create the slope chart as shown in the image, you need to ensure that the X-axis contains categorical values like in the small Excel table you attached. Specifically, the categories "Current" and "Previous" should be part of a single categorical column in your data.
Instead of having each time period as a separate column, you need a single column that contains values like "Current" and "Previous." This will allow you to plot the data in a line chart (slope chart), where the X-axis represents these categorical values.
If you've already built logic in your table that returns a value of '1' based on the month, you could modify it so that it returns "Current" or "Previous" instead, to use as categories.
Metrics will be at the legend
To provide more specific help, it would be beneficial to see exactly what your original table looks like. So, similar to the required table you shared, consider sharing the original table as well.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
2 Replies
Hi dcheng029
If I understood correctly, in order to create the slope chart as shown in the image, you need to ensure that the X-axis contains categorical values like in the small Excel table you attached. Specifically, the categories "Current" and "Previous" should be part of a single categorical column in your data.
Instead of having each time period as a separate column, you need a single column that contains values like "Current" and "Previous." This will allow you to plot the data in a line chart (slope chart), where the X-axis represents these categorical values.
If you've already built logic in your table that returns a value of '1' based on the month, you could modify it so that it returns "Current" or "Previous" instead, to use as categories.
Metrics will be at the legend
To provide more specific help, it would be beneficial to see exactly what your original table looks like. So, similar to the required table you shared, consider sharing the original table as well.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Hi,Ritaf1983 ,thanks for your concern about this issue.
Your answer is excellent!
And I would like to share some additional solutions below.
Hello,dcheng029 .I am glad to help you.
It looks like you need to do some processing on the original data.
I have created the following test data (raw data) based on the matrix presentation you provided.Metrics Classify Values Metric 1 Current Month 9 Metric 2 Current Month 6 Metric 3 Current Month 4 Metric 1 Previous Month 32 Metric 2 Previous Month 0 Metric 3 Previous Month 13 Metric 1 Previous 2 Month 46 Metric 2 Previous 2 Month 0 Metric 3 Previous 2 Month 9 Metric 1 Current vs Previous Difference -23 Metric 2 Current vs Previous Difference 6 Metric 3 Current vs Previous Difference -9 Metric 1 Current vs Previous 2 Difference -14 Metric 2 Current vs Previous 2 Difference 0 Metric 3 Current vs Previous 2 Difference 4 Depending on the form you want to get
I did the following, I deleted rows one to nine
2. Change the data in the Classfiy column and generate a new column by if function.
create three measure:
Here is my test code:Metric_1 = CALCULATE(MAX('TestData'[Values]),FILTER('TestData','TestData'[Metrics]="Metric 1")) Metric_2 = CALCULATE(MAX('TestData'[Values]),FILTER('TestData','TestData'[Metrics]="Metric 2")) Metric_3 = CALCULATE(MAX('TestData'[Values]),FILTER('TestData','TestData'[Metrics]="Metric 3"))The result:
Below is my M code:let Source = Excel.Workbook(File.Contents("C:\Users\username\Desktop\test_11_11.xlsx"), null, true), OriginalData_Sheet = Source{[Item="OriginalData",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(OriginalData_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Metrics", type text}, {"Classify", type text}, {"Values", Int64.Type}}), #"Removed Alternate Rows" = Table.AlternateRows(#"Changed Type",0,9,6), #"Added Custom" = Table.AddColumn(#"Removed Alternate Rows", "Classify2", each if [Classify] = "Current vs Previous Difference" then "Current" else if [Classify] = "Current vs Previous 2 Difference" then "Previous" else null), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Classify"}) in #"Removed Columns"I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
