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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hemingt
Helper V
Helper V

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
Anonymous
Not applicable

@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
Anonymous
Not applicable

@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

Hi @Anonymous 

 

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

Hello @Anonymous 

Do you have any idea?

Anonymous
Not applicable

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

Line Chart.PNG

yes, @Anonymous  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

Anonymous
Not applicable

@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

Hello @Anonymous 

 

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

Anonymous
Not applicable

@hemingt - Did you use the week column from the new table, instead of the original week column? Here is how it looks on my end now - note that the red line is initially flat, instead of angled.

Line Chart 2.PNG

Hello @Anonymous 

I may get some misunderstanding about the description.

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

Can you explain the detail steps or can you provide the pbix file? Thank you very much! 

@Anonymous You're right. I made a mistake while I create the Week table.

Now I can have the same chart with you!

Thank you very much for your support!

Thank you very much @Anonymous 

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

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

Thank you @Anonymous 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.