Plotting Calculated Column Months into a stack column chart

[AngusTan]

Hello,

I'm a new PowerBI user who just started learning this program.

I'm working on a resource loading visualization that pulls data from a database and applies a function to calculate the load of each item at the particular month. I have a 12 calculated columns (Months of 2020) which I would like to plot on a line chart showing the sum of the data in each month over the year.

However, I can't seem to find the correct x-axis to plot this data.

Sorry if this has been raised, I've been searching the forums but unable to find a solution which works for me.

Thanks!

[AngusTan]

Thanks everybody for the replies.

Unfortunately as my data is confidential I am unable to share it. I've created a dummy database in excel as shown here.

Columns A-G are fixed and queried from an online database. Columns H and beyond are my calculated columns done in Power BI,  they display the load of each project at a particular month. A project progresses along stages and naturally the load also changes. My intent is to eventually achieve a Visual where I can filter by the appropriate manager and it shows a timeline of his/her total load per month.

I thought I could separate the monthly loads on another table but I couldn't get it to refer to the main dataset (which contains the project stage for load calculation)

From what I understand from the previous advice, it seems that I need to duplicate 12 entries per Project? Wouldn't that be far too complicated?

Thanks again to all who had replied.

[v-jayw-msft]

Hi @AngusTan ,

I'm not sure how you define "Load". For example, why the jan 2020 load in the first row is 5. Appreciate for your explanation.

Best regards,

Jay

[v-jayw-msft]

Hi @AngusTan ,

Please refer to the formula below to create a calculated table.

BTW, it would be better to share some sample data to us if you don't have any Confidential Information. In that case we might be able to help you find another way instead of creating 12 calculated column.

Table 2 = 
UNION (
    SELECTCOLUMNS (
        'Table',
        "item", 'Table'[Item],
        "person", 'Table'[Person],
        "amount", 'Table'[Jan],
        "month", "Jan"
    ),
    SELECTCOLUMNS (
        'Table',
        "item", 'Table'[Item],
        "person", 'Table'[Person],
        "amount", 'Table'[Feb],
        "month", "Feb"
    )
)

 Result would be shown as below.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mariusz]

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.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[AngusTan]

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

[Ashish_Mathur]

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.