Forum Discussion
Plot calculated columns
Hi,
I need to plot the following: x-values are months from 1 to 24, and y-values are values (for example the sum) calculated in different collumns of the table.
In my case I have a table with a column of projects (Proyecto) and several columns in which I distributed the estimated incomes for several months (m1, m2, m3 .... up to m24)
Also, each project will be develop by different teams in the company (column Uni Subproyecto)
I need to plot as x-values the different months (just considering the numbers 1 to 24). As the y-values I need to consider the sum per each type of team (column Uni Subproyecto) for the 24 month columns (m1, m2, m3 ... m24)
x: 1, 2, 3, 4, .... 24
y: sum of IES in m1, sum of IES in m2, sum of IES in m3, sum of IES in m4, ...., sum of IES in m24
I don't know how to do it,
Really appreciate for any help
Greetings,
Hi , ivial
Here are the steps you can refer to :
(1)This is my test data :
(2)We need to unpivot your [m1]..[m24] columns in Power Query Editor, the m language is like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZO7DoJQEAX/hZqCu/f9LYTCGEvUwsa/N8CykZw9heyEM9PhPA/31/q+Pb9hGIftJ879PG5rGJbRZNEhOhfkqENyLshJh+zcTZZ/OetQnAty0aE6F+SqQ3MuyE2H7lyQuw5hcgH8/e3xCISwCSYJIWzEpEgIm2hSIrQ18dIkkzIhbLJJhRA2xaRKCD6+Y98fjRA2zaROCJt+TjIRwr/cdE4SCGETTBJC2IhJkRA20aRECJtkUiaETTapEMKmmFQJYVNNaoS2Jl2aZlInhE0/pzgRgmZ/ezwCIW2WHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Proyecto = _t, m1 = _t, m2 = _t, m3 = _t, m4 = _t, m5 = _t, m6 = _t, #"Uni Subproyecto" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Proyecto", type text}, {"m1", Int64.Type}, {"m2", Int64.Type}, {"m3", Int64.Type}, {"m4", Int64.Type}, {"m5", Int64.Type}, {"m6", Int64.Type}, {"Uni Subproyecto", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Proyecto", "Uni Subproyecto"}, "Month", "Value") in #"Unpivoted Columns"The result is as follows:
(3)We can create a calculated column in Power Bi Desktop in our table to get the "Month_Number":
Month_Number = RIGHT('Table'[Month],1)(4)Then we just need to put the filed we need on the visual and we will meet your need :
If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi, ivial
You can also unpivot the table using dax , like this:
[9:04 AM] AniyaZhang Table 2 = UNION( SELECTCOLUMNS('CLickUP_CRM_SVE' , "Teams" , [SVE.Uni Subproyecto] ,"m" , 1 , "m_value" , [m1]) , SELECTCOLUMNS('CLickUP_CRM_SVE' , "Teams" , [SVE.Uni Subproyecto] ,"m" , 2 , "m_value" , [m2]))For more information, you can refer to :
https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-Unpivot/td-p/574832
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
5 Replies
Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hi , ivial
Here are the steps you can refer to :
(1)This is my test data :
(2)We need to unpivot your [m1]..[m24] columns in Power Query Editor, the m language is like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZO7DoJQEAX/hZqCu/f9LYTCGEvUwsa/N8CykZw9heyEM9PhPA/31/q+Pb9hGIftJ879PG5rGJbRZNEhOhfkqENyLshJh+zcTZZ/OetQnAty0aE6F+SqQ3MuyE2H7lyQuw5hcgH8/e3xCISwCSYJIWzEpEgIm2hSIrQ18dIkkzIhbLJJhRA2xaRKCD6+Y98fjRA2zaROCJt+TjIRwr/cdE4SCGETTBJC2IhJkRA20aRECJtkUiaETTapEMKmmFQJYVNNaoS2Jl2aZlInhE0/pzgRgmZ/ezwCIW2WHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Proyecto = _t, m1 = _t, m2 = _t, m3 = _t, m4 = _t, m5 = _t, m6 = _t, #"Uni Subproyecto" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Proyecto", type text}, {"m1", Int64.Type}, {"m2", Int64.Type}, {"m3", Int64.Type}, {"m4", Int64.Type}, {"m5", Int64.Type}, {"m6", Int64.Type}, {"Uni Subproyecto", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Proyecto", "Uni Subproyecto"}, "Month", "Value") in #"Unpivoted Columns"The result is as follows:
(3)We can create a calculated column in Power Bi Desktop in our table to get the "Month_Number":
Month_Number = RIGHT('Table'[Month],1)(4)Then we just need to put the filed we need on the visual and we will meet your need :
If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thank you very much for your reply. Nevertheless, the problem I have is that I don't have columns m1 to m24 in the Power Query Editor since they are calculated columns and they don't appear in the editor (or maybe I don't know how to do it).
So, how could I manage or ("unpivot") these calculated columns? I share the link with my table data.
Appreciate if you someone could clarify how could I plot:
x-values: 1, 2, 3, 4, .... 24
y-values: sum of IES in m1, sum of IES in m2, sum of IES in m3, sum of IES in m4, ...., sum of IES in m24
the link is the following: https://sirvesa-my.sharepoint.com/:f:/g/personal/ivial_sirve_cl/EpxokCAuhodBidtSZl2qRkQB11TdxdC5r0xW1iZxQahTfg?e=lL0sW2
Thanks a lot!
Greetings,
Hi, ivial
You can also unpivot the table using dax , like this:
[9:04 AM] AniyaZhang Table 2 = UNION( SELECTCOLUMNS('CLickUP_CRM_SVE' , "Teams" , [SVE.Uni Subproyecto] ,"m" , 1 , "m_value" , [m1]) , SELECTCOLUMNS('CLickUP_CRM_SVE' , "Teams" , [SVE.Uni Subproyecto] ,"m" , 2 , "m_value" , [m2]))For more information, you can refer to :
https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-Unpivot/td-p/574832
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thank you very much Aniya, that worked ok!
Greetings,
