Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
ivial
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)

ivial_0-1666964135205.png

Also, each project will be develop by different teams in the company (column Uni Subproyecto)

ivial_1-1666964248808.png

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
v-yueyunzh-msft
Community Support
Community Support

Hi , @ivial 

Here are the steps you can refer to :

(1)This is my test data :

vyueyunzhmsft_0-1667181715473.png

(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:

vyueyunzhmsft_1-1667181780437.png

(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 :

vyueyunzhmsft_2-1667181844573.png

 

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

 

View solution in original post

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

 

 

View solution in original post

5 REPLIES 5
ivial
Regular Visitor

Thank you very much Aniya, that worked ok!

 

Greetings,

v-yueyunzh-msft
Community Support
Community Support

Hi , @ivial 

Here are the steps you can refer to :

(1)This is my test data :

vyueyunzhmsft_0-1667181715473.png

(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:

vyueyunzhmsft_1-1667181780437.png

(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 :

vyueyunzhmsft_2-1667181844573.png

 

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

 

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

 

 

lbendlin
Super User
Super User

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors