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 am looking to create a graph that shows the status of different items (rows of data) based on the dates of different activities that are included in the columns.
So, I would want on the x axis to be the month and on the y axis would be the count of instances. And it would be a stacked bar chart showing the count of items in each "Initial Work," "Pending," "Approved," and "Complete" on a given date. So for 1/1/19 I would see just a count of 3 for initial work. For 4/1/19 you would see 1 initial work, 3 pending, 5 approved, 3 complete in a stacked bar chart.
I am really just looking for some direction on the best way to tackle this problem. I am assuming I will need to have measures to count on a specific date saying if complete and approved are blank, then count, etc..., but I am not sure the best way to set it up (or if it is even possible??). Thank you in advance!!
Also - wanted to add that I can see how you could do this in excel easily with a few countif functions and create a table based on dates and use the second table for the chart... but this is a part of a larger project where we are setting up a dashboard, so if there is a way to keep it in BI that would be preferred.
Solved! Go to Solution.
Yes, I was able to find a solution! Basically I did the following:
Let me know if that helps or if you need any clarification!
Hi @swiley9 ,
Select the Name column and unpivot Others columns as below.
M code for your reference.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUN9Q3MjC0RGcaI5gmEGasTrSSE1jAiJAOU4QOZ3RlRhgG6yiBVboQoxLFNa7oyoyxakZyjRu6HHamGUKHO6rBeFR6oMrhUemJxUwTrCq90MIJgkAy3ugeRjYOrMIHZ+jAVPjiiGmECj8coY8jTmIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, #"Initaial Work" = _t, Pending = _t, Approved = _t, Complete = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Initaial Work", type date}, {"Pending", type date}, {"Approved", type date}, {"Complete", type date}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name"}, "Attribute", "Value")
in
#"Unpivoted Other Columns"
Then we can create starcked column chart after applying the steps in power query.
For more details, please check the pbix as attached.
Yes, I was able to find a solution! Basically I did the following:
Let me know if that helps or if you need any clarification!
Thank you very much, kudos to you!
Any chance of sharing a PBIX?
You will have to unpivot the columns and convert them to row. e..g https://www.newtechdojo.com/unpivot-columns-in-power-bi/
Then use stacked column chart: https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-combo-chart
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 |
---|---|
96 | |
95 | |
83 | |
70 | |
66 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |