cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sya
Helper I
Helper I

Sum of multiple column in power bi

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:

 

sya_0-1646214671803.png

Any help is greatly appreciated!

 

Sya

 

2 ACCEPTED SOLUTIONS

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.

View solution in original post

v-angzheng-msft
Community Support
Community Support

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:

vangzhengmsft_0-1646630494446.png

 

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.

View solution in original post

5 REPLIES 5
v-angzheng-msft
Community Support
Community Support

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:

vangzhengmsft_0-1646630494446.png

 

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
Resolver II
Resolver II

Hi @sya 

 

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

 

measure = SUM(Starter) + SUM(Leaver)

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

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.

Hi @coskuersanli ,

 

  • Add a new column and call it starter.

What type of column?

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors