Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 | Title | Plan_Start_Week | Actual_Start_Week | Plan_End_Week | Actual_End_Week |
Kye-1 | Title-1 | 1922 | 1921 | 1930 | 1930 |
Kye-2 | Title-2 | 1921 | 1923 | 1935 | |
Kye-3 | Title-3 | 1923 | 1923 | 1928 | 1929 |
Kye-4 | Title-4 | 1922 | 1922 | 1925 | 1926 |
Kye-5 | Title-5 | 1933 | 1940 | ||
Kye-6 | Title-6 | 1924 | 1923 | 1945 | |
Kye-7 | Title-7 | 1935 | 1942 | ||
Kye-8 | Title-8 | 1923 | 1922 | 1930 | 1928 |
Kye-9 | Title-9 | 1927 | 1927 | 1935 | |
Kye-10 | Title-10 | 1924 | 1924 | 1929 | 1929 |
Kye-11 | Title-11 | 1925 | 1925 | 1930 | 1928 |
Kye-12 | Title-12 | 1924 | 1923 | 1929 | 1927 |
Here is the demo excel link https://1drv.ms/x/s!AsM9bO8zGUN6qzXIG6TOWR9dlewD?e=TN9Xeu
Solved! Go to Solution.
@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
@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
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.
Hello @Anonymous
Do you have any idea?
@hemingt - I just retried the 3 steps that I described, and I got the following result:
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?
@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.
Hello @Anonymous
unfortunately, I got the same result as previous. Can you have a try?
@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.
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.
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.
I changed the datatype of Week from text to whole number, the line becomes what I want.
Thank you @Anonymous
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |