Sum of multiple column in power bi

[sya]

Hi team,

I have to calculate the sum of each column and need to present in line chart by month. I've spent hours on this and now the only way I know is to make a new table with sum of each column in excel before importing to power bi.

Any way to apply formulas in power bi to the original dataset?

https://1drv.ms/x/s!AjXpGcOFDGB2iFxaCJdrANJ8p1Yt?e=ecyS7E

This is the desired output:

Any help is greatly appreciated!

Sya

[coskuersanli · Accepted solution]

If you can seperate your starter and leaver data (different sheets e.g) it would be a lot easier.

• Select all month columns and unpivot them.
• Add a new column and call it starter.
• Do the same thing for leavers.
• Lastly append 2 table and create new one.

[v-angzheng-msft · Accepted solution]

Hi, @sya 

You can follow the solution below, which is the same as @coskuersanli 's idea, but you don't need to modify your data source, you can do all the transformations in PowerQuery.
Create a reference copy in PowerQuery to get the Starter and Leaver tables. Then append the two tables as a new table.

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[coskuersanli]

Hi @sya 

You can create a measure. Can you try this one, please?

measure = SUM(Starter) + SUM(Leaver)

[coskuersanli]

I've just seen your data, it looks like you have to unpivot it in Power Query 🙂

[coskuersanli · Accepted solution]

If you can seperate your starter and leaver data (different sheets e.g) it would be a lot easier.

• Select all month columns and unpivot them.
• Add a new column and call it starter.
• Do the same thing for leavers.
• Lastly append 2 table and create new one.

[sya]

Hi @coskuersanli ,

• Add a new column and call it starter.

What type of column?