Plot calculated columns

[ivial]

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,

[v-yueyunzh-msft · Accepted solution]

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

[ivial]

Thank you very much Aniya, that worked ok!

Greetings,

[v-yueyunzh-msft · Accepted solution]

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

[ivial]

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/EpxokCAuhodBidtSZl2qRkQB11TdxdC5r0xW... 

Thanks a lot!

Greetings,

[v-yueyunzh-msft · Accepted solution]

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

[lbendlin]

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-...
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/1447523