Forum Discussion
Plotting Calculated Column Months into a stack column chart
Hi AngusTan
Why not store the Months as rows instead of Columns?
Its a lot easier to handle data in this format in Power BI.
Mariusz
If this post helps, then please consider Accepting it as the solution.
Hey Mariusz ,
Thanks for the quick reply!
The reason why I didn't store the months as rows is because the rows are already populated with a list of items. I've plotted an example below.
Thus the intent would be to display a Column or Area Chart, showing the Total number of fruits purchased by a person per month. In Jack's case, I should be able to visualise the total number of fruits he purchased every month, over the entire year; i.e; 14 fruits in Jan, 7 Fruits in Feb and so on...
I can plot it using a Clustered Column chart but I am unable to format the axis labels with months as per traditional timeline charts.
Columns 'Item' and 'Person' are fixed and obtained from an online database, and there are several columns I used to calculate the number of fruits purchased by a particular person in a particular month (actual number of rows, additional columns not shown for simplicity).
Thanks!
Item Person Jan Feb Apple Jack 10 5 Orange Tom 1 1 Pear Jill 2 4 Banana Jack 4 2 Hi,
As suggested, it would still be the best layout to get all months in a single column. We will then build a date column and a Calendar Table. Then create a relationship from the Date column of the Data Table to the Date column of the Calendar Table. In the Calendar Table, write calculated column formulas to extract Year and Month. To your visual, drag Year and Month from the Calendar Table. Write measures and then drag those to your visual.
