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've got this data from SQL server where it comes in this form
Jan | Feb | Mar | Apr | May | June | July | Aug | Sept | Oct | Nov | Dec
I am trying to represent it in a chart like shown below but it does not have an X axis so the chart always comes out looking this tight. I have tried to use vizualization section to space it out to no avail. Users do not like the chart in this fashion. Any advice would be helpful.
Solved! Go to Solution.
Hi @SimonSeez ,
Based on your description, select the Name column in power query and use 'Unpivot other columns', rename the Attribute column as 'Month' column, close and apply it.
Create a calculated column like this to show the month by sort in the visual:
_Month =
SWITCH (
[Month],
"Jan", 1,
"Feb", 2,
"Mar", 3,
"Apr", 4,
"May", 5,
"Jun", 6,
"Jul", 7,
"Aug", 8,
"Sept", 9,
"Oct", 10,
"Nov", 11,
"Dec", 12
)
Change the X-axis type to Categorical:
Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@SimonSeez , You said 5000 Columns, seem like they Rows. Rows does not matter. I want unpivot not transpose (If I was not clear in last update)
refer: https://youtu.be/2HjkBtxSM0g
@SimonSeez , are these month columns or other column. If they are the only months they better to unpivot or transpose. can you give example of other columns
Hello @amitchandak
They are months on the columns (Jan to Dec ) and names of staff on the rows. The months contain financial data of value of business brought by each staff in a month.
The chart I am trying to make is showing the monthly trend so I use RLS to ensure each staff sees trends for only their columns.
Best Regards,
@SimonSeez , better to unpivot the data and then combine with year if needed and create a date.
https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/
Hello @amitchandak
Thank you for your feedback . I think I might have missed out some information that would make transposing it less of an option.
I actually get the data this way
Name | Jan | Feb | xxx | xxx | Dec
Simon | 999.9 | 456 | 3455 | 653
Thomas | 7888 | 8772 | 8872 | 888
And I have about 5,000 columns with different names. Would your recommendation still be to transpose?
Best Regards .
Hi @SimonSeez ,
Based on your description, select the Name column in power query and use 'Unpivot other columns', rename the Attribute column as 'Month' column, close and apply it.
Create a calculated column like this to show the month by sort in the visual:
_Month =
SWITCH (
[Month],
"Jan", 1,
"Feb", 2,
"Mar", 3,
"Apr", 4,
"May", 5,
"Jun", 6,
"Jul", 7,
"Aug", 8,
"Sept", 9,
"Oct", 10,
"Nov", 11,
"Dec", 12
)
Change the X-axis type to Categorical:
Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you very much @v-yingjl
This works but how do I get the chart to calender months rather than month count.
Best Regards
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 |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |