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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Jaehir
Frequent Visitor

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:

 

Jaehir_0-1712220972287.png

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. 

 

Jaehir_1-1712221171833.png

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.

 

1 ACCEPTED SOLUTION

Hi @Jaehir ,

 

Thanks for the reply from @johnbasha33 .

 

See which one you like?

vhuijieymsft_0-1712545505000.png

 

Or:

vhuijieymsft_1-1712545505003.png

 

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:

vhuijieymsft_0-1712545646415.png

 

The second visual object needs to be like this:

vhuijieymsft_1-1712545646417.png

 

The pbix file is attached.

 

If you have any other questions please feel free to contact me.

 

Best Regards,
Yang
Community Support Team

 

If 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!

View solution in original post

10 REPLIES 10
johnbasha33
Solution Sage
Solution Sage

@Jaehir i agree with th above solution.

v-huijiey-msft
Community Support
Community Support

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:

vhuijieymsft_0-1712306377090.png

 

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 Team

 

If 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.

Screenshot 2024-04-05 174701.png

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?

vhuijieymsft_0-1712545505000.png

 

Or:

vhuijieymsft_1-1712545505003.png

 

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:

vhuijieymsft_0-1712545646415.png

 

The second visual object needs to be like this:

vhuijieymsft_1-1712545646417.png

 

The pbix file is attached.

 

If you have any other questions please feel free to contact me.

 

Best Regards,
Yang
Community Support Team

 

If 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!

This is great! First one is exactly the visual i was looking for. Thanks a lot to everyone for helping me out. Now i can move forward on creating my company's desired dashboard.  

Sergii24
Super User
Super User

Hi @Jaehir, you might be interested in learning more on "Unpivot" functionality, which will help you to obtain 2 columns: one with machine number and the second one with corresponding values. Then you can use "Machine Type" as a legend.

You can find learn more about "Unpivot" here.


Have a great day!

  • 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 so far ive tried using unpivot and it works well. The date followed through correctly for each IN and OUT. But i have an issue, when i unpivot the second sets of column, Machine 2 (IN),..Machine 2 (OUT), the table becomes a lot messier with too much duplication. I tried research online but whatever i tried like creating copied queries and merged queries, or unpivot both sets of column and then split column with delimeter, doesnt seems to succeed.

 

Jaehir_0-1712289781415.png

 

Understood tomorrow I'll try recreating the graph. I'll be updating my progress. Thanks for the assist.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors