cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## 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,

2 ACCEPTED SOLUTIONS
Community Support

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

Community Support

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]))

``````

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 5
Regular Visitor

Thank you very much Aniya, that worked ok!

Greetings,

Community Support

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

Regular Visitor

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,

Community Support

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]))

``````

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

Super User

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.