Forum Discussion
Clustered column chart for machine input and output..
Hello,
I am very new to power bi and my superior insist on me learning on how to use this without prior training. So I really appreciate with all the help I can get.
I have a set of table which obtained from excel file that I've made:
Each date will have its shift and the machines that runs on that particular shift. Each machine will have it's material input represent as IN and how much material that passed through represent as OUT. If there's amount of material that's not passed, it will consider as SCRAP.
For this i need to create a graph bar onto my canvas though i can only understand some of its function. For example I can create a slicer for DATE field and make it so that data can synchronize with the date selected. But for the Machines, I'm using clustered column chart but im not sure on how to do this.
This is the example chart that I felt the best suited for this table. On X-Axis, Paseo, VTT, Amarilla can be replace to Machine 1, Machine 2, Machine 3. The legend would be the IN and OUT data of that machine. Y-Axis from my understanding will follow the maximum and minimum data based on the IN and OUT. The SCRAP however will not be as important so long that the amount of IN and OUT can be seen.
I hope this can be worked out since I want to further study on this software for future reference.
Hi Jaehir ,
Thanks for the reply from johnbasha33 .
See which one you like?
Or:
The first visual object requires the creation of a calculated column:
Machine = SWITCH( TRUE(), CONTAINSSTRING([Attribute], "Machine 1"), "Machine 1", CONTAINSSTRING([Attribute], "Machine 2"), "Machine 2", CONTAINSSTRING([Attribute], "Machine 3"), "Machine 3", "Unknown" )Then:
The second visual object needs to be like this:
The pbix file is attached.
If you have any other questions please feel free to contact me.
Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
10 Replies
- Thanks for the respond. I have a question, how about the Date with different time correspond to each machine operating that day? Since if i unpivot the columns, wouldnt the Date need to be effected as well? Also for legend, instead of machine type, i need it to be IN and OUT, for example, one bar represent IN (color light blue) and one bar respresent OUT (color dark blue). This has been bugging me while trying to create the graph itself.
From what I see at your screenshot the date should be okay: you already have all dates in the same column. When unpivoting columns, for unselected columns values will repeat, so if you 1 row with a single date for 3 machines and their values, the unpivoted version will have 3 rows (by number of machines), where the same date will be repeated.
What I suggested (unpivot machines) is just an example. Play around with unpivoting to obtain the desired result. Remember that to use any value in a legend they all should be within the same single column.
Hi Jaehir ,
I would like to acknowledge the valuable input provided by Sergii24 .
His initial ideas were instrumental in guiding my approach.
In my investigation, I took the following steps:
After the unpivot, creates a calculated column:
Type = SWITCH( TRUE(), CONTAINSSTRING([Attribute], "(IN)"), "IN", CONTAINSSTRING([Attribute], "(SCRAP)"), "SCRAP", CONTAINSSTRING([Attribute], "(OUT)"), "OUT", "Unknown" )Drag DATE to the X-axis, Value to the Y-axis, and the created calculated column to Legend.
If you do not want to show SCRAP, just filter it out in Filter.
The final page result is shown below:
The pbix file is attached.
If you have any other questions please feel free to contact me.
I would be grateful if you could provide me with sample data for testing, please remove any sensitive data in advance.
Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!The graph is exactly what i nearly wanted. Though on the x axis, is there any way that i can have for each grouped bar (IN and OUT), instead of having date, i can have the machine type. For example, the 29 Feb, 12AM replaced with Machine 1 and the corresponding bar would be the other machine type like Machine 2, Machine 3. I tried unpivoting the Machine 1 (IN) and Machine 1 (OUT) and rename the attribute into Machine 1, while the table is exactly what i wanted, when I unpivot Machine 2 (IN) and Machine 2 (OUT), the column Machine 1 became messy.
I imagine if the attribute (Machine 1), (Machine 2), (Machine 3), can be inserted onto x axis, the data will represent each of the other machine and the bar (Machine 1 Value), (Machine 2 Value), (Machine 3 Value), represent each IN and OUT values. Sorry if im wrong in this but thats how i imagine it.
Hi Jaehir ,
Thanks for the reply from johnbasha33 .
See which one you like?
Or:
The first visual object requires the creation of a calculated column:
Machine = SWITCH( TRUE(), CONTAINSSTRING([Attribute], "Machine 1"), "Machine 1", CONTAINSSTRING([Attribute], "Machine 2"), "Machine 2", CONTAINSSTRING([Attribute], "Machine 3"), "Machine 3", "Unknown" )Then:
The second visual object needs to be like this:
The pbix file is attached.
If you have any other questions please feel free to contact me.
Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
Jaehir i agree with th above solution.
