Forum Discussion
Creating a table for daily plan vs. actual data
Hi!
I'm working at a manufacturing company and I'm new to Power BI. I'm trying to create a dashboard containing a simple table and need some help.
My raw data in Excel spreadsheet looks like below. Dates are in rows, and columns are for plan/actual/delta(actual-plan) per machines. The values are production quantites.
The table I want to create in Power BI should look like below. There's a slicer for the date and the values in the table change per the date.
How can I achieve my goal? Should I change my raw data setup? If so, how?
I'd appreciate some hints. Thank you!
Hi, Anonymous
You can try the following methods. Choose Date column then unpivot Other Columns:
Result:
Select the second column-Split Column:
Result:
Save and apply to Desktop.
Is this the result you expected?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hints:
- do not change your source data (unless there is some other reason to do so)
- investigate Power Query unpivot against your raw data.
- This will transpose your raw data into the shape you need (a table containing date, machine, attribute (ie plan or actual), amount)
- don't load the delta column from your raw data. Calculate it using dax
- Use a matrix to visualise your data
Hi, Anonymous
You can try the following methods. Choose Date column then unpivot Other Columns:
Result:
Select the second column-Split Column:
Result:
Save and apply to Desktop.
Is this the result you expected?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
Thank you so much, that exactly is what I wanted! Now I'm trying to reorder the columns by Plan/Actual/Delta but it seems it's not that simple in Matrix. I created another column as index. Plan=1, Actual=2, Delta=3. Then I tried to sort the column using that index but I'm getting an error. Could you shed some light on this?
