cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hemingt Member
Member

How to generate the cumulative line chart with such data?

Hello,

 

I have the below demo data, I want to generate a Chart with cumulative data.

There are four lines, Plan_start, Actual_Start, Plan_End and Actual_End. 

The x-axis data is the Week, the y-axis data is the count of the key, the count of Key which Plan_Start_Week equals to the x-axis data, the count of Key which Actual_Start_Week equals to the x-axis data, ...

for example:

x-axis data: 1921

y-axis data: count of Plan_Start_Week is 1, count of Actual_Start_Week is 1 , count of Plan_End_Week is 0, count of Actual_End_Week is 0

x-axis data: 1922

y-axis data: count of Plan_Start_Week is 3, count of Actual_Start_Week is 3 , count of Plan_End_Week is 0, count of Actual_End_Week is 0

x-axis data: 1923

y-axis data: count of Plan_Start_Week is 5, count of Actual_Start_Week is 7 , count of Plan_End_Week is 0, count of Actual_End_Week is 0

Do you have any idea to generate such chart? 

Key  TitlePlan_Start_WeekActual_Start_WeekPlan_End_WeekActual_End_Week
Kye-1Title-11922192119301930
Kye-2Title-2192119231935 
Kye-3Title-31923192319281929
Kye-4Title-41922192219251926
Kye-5Title-51933 1940 
Kye-6Title-619241923 1945
Kye-7Title-71935 1942 
Kye-8Title-81923192219301928
Kye-9Title-9192719271935 
Kye-10Title-101924192419291929
Kye-11Title-111925192519301928
Kye-12Title-121924192319291927

 

Here is the demo excel link https://1drv.ms/x/s!AsM9bO8zGUN6qzXIG6TOWR9dlewD?e=TN9Xeu

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: How to generate the cumulative line chart with such data?

@hemingt  -

Try the following steps and let us know:

1.  To start, I'd Unpivot the 4 week columns in Power Query. (Note: Unpivot is always something to consider when different columns contain very similar or the same information). Here is an M script, starting with the data you shared:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZE/D4IwEMW/CumMSf9SOjo4uZho4kAIIdrBSBhMHfj21pZeexinV64/ru/edR052qWqSE0uDzdZr6dpnIezG19uuFr79JX9zb3HCdcCdZjvGwYqfe07L3bHUudwYobzKPFL0CSJ58AjkIsIKi8VwAJgUVJJ2igGeAm8RGZWUVEa4BXw8UqI8Pz3KCly0gDZxCYyO1l/UABrgHU5VaA4atsC2aLJeJmenzPxBngTr3Qpm/QYzbuhyLVMwW3yY8U2GcpM/THE8j4Z/0kmv6FJ338A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Key  ", type text}, {"Title", type text}, {"Plan_Start_Week", Int64.Type}, {"Actual_Start_Week", Int64.Type}, {"Plan_End_Week", Int64.Type}, {"Actual_End_Week", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Title", "Key  "}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Week Type"}, {"Value", "Week"}})
in
    #"Renamed Columns"

2. Add the following measure:

Cumulative Count = 
var __week = MAX('Table'[Week])
return CALCULATE(
    COUNTROWS('Table'),
    'Table'[Week] <= __week
)

3. Add the following to a line chart.

Axis: Week

Legend: Week Type

Value: Cumulative Count

 

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.
Nathan Peterson

View solution in original post

12 REPLIES 12
Highlighted
Super User II
Super User II

Re: How to generate the cumulative line chart with such data?

@hemingt  -

Try the following steps and let us know:

1.  To start, I'd Unpivot the 4 week columns in Power Query. (Note: Unpivot is always something to consider when different columns contain very similar or the same information). Here is an M script, starting with the data you shared:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZE/D4IwEMW/CumMSf9SOjo4uZho4kAIIdrBSBhMHfj21pZeexinV64/ru/edR052qWqSE0uDzdZr6dpnIezG19uuFr79JX9zb3HCdcCdZjvGwYqfe07L3bHUudwYobzKPFL0CSJ58AjkIsIKi8VwAJgUVJJ2igGeAm8RGZWUVEa4BXw8UqI8Pz3KCly0gDZxCYyO1l/UABrgHU5VaA4atsC2aLJeJmenzPxBngTr3Qpm/QYzbuhyLVMwW3yY8U2GcpM/THE8j4Z/0kmv6FJ338A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Key  ", type text}, {"Title", type text}, {"Plan_Start_Week", Int64.Type}, {"Actual_Start_Week", Int64.Type}, {"Plan_End_Week", Int64.Type}, {"Actual_End_Week", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Title", "Key  "}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Week Type"}, {"Value", "Week"}})
in
    #"Renamed Columns"

2. Add the following measure:

Cumulative Count = 
var __week = MAX('Table'[Week])
return CALCULATE(
    COUNTROWS('Table'),
    'Table'[Week] <= __week
)

3. Add the following to a line chart.

Axis: Week

Legend: Week Type

Value: Cumulative Count

 

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.
Nathan Peterson

View solution in original post

hemingt Member
Member

Re: How to generate the cumulative line chart with such data?

Thank you very much @natelpeterson 

your idear works well. still one point need you help to improve the solution. I did as you suggest and I got the below chart.

360截图--1367220625.jpg

 

as you can see the line was broken. Is it possible to generate the chart as below, take the Plan_End_Week (red line) as example, there is no data at Week 1926 and 1927, since it is a cumulative data, we should set the data as Week 1925.

360截图--1367092687.jpg

hemingt Member
Member

Re: How to generate the cumulative line chart with such data?

I changed the datatype of Week from text to whole number, the line becomes what I want.

Thank you @natelpeterson 

hemingt Member
Member

Re: How to generate the cumulative line chart with such data?

Hi @natelpeterson 

 

So sorry. I double checked the data, and find that the problem still existed.

as you can see, if certain type_week has no data on certain week, the data will be blank.

Do you know how to solve this problem? I want Actual_End_Week at week 1927 also show as 1.

 

360截图--1362859656.jpg

hemingt Member
Member

Re: How to generate the cumulative line chart with such data?

Hello @natelpeterson 

Do you have any idea?

Super User II
Super User II

Re: How to generate the cumulative line chart with such data?

@hemingt  - I just retried the 3 steps that I described, and I got the following result:

Line Chart.PNG

hemingt Member
Member

Re: How to generate the cumulative line chart with such data?

yes, @natelpeterson  I have the same Chart with you.

But as you can see , let's take the the Actual_End_Week as example, there is no data at Week 1927, I want to the cumulative value at 1927 should be 1, as the same as Week 1926.  Do you get my concern?

360截图--1278462718.jpg

Super User II
Super User II

Re: How to generate the cumulative line chart with such data?

@hemingt  - Ah, you need another table. You can either make a Date table, or a Week table. You can make the latter like this:

Weeks = VALUES('Table'[Week])

-Make a relationship between the 2 tables.

-Modify the measure to the following:

Cumulative Count = 
var __week = MAX('Weeks'[Week])
return CALCULATE(
    COUNTROWS('Table'),
    'Weeks'[Week] <= __week
)

-Change the Axis in the line chart to the column from the new table.

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.
Nathan Peterson
hemingt Member
Member

Re: How to generate the cumulative line chart with such data?

Hello @natelpeterson 

 

unfortunately, I got the same result as previous. Can you have a try?

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors