cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
malynnej
Frequent Visitor

Sum rows in dataset

Hello,

I have an Excel-dataset which looks like this:

 

4 Food260
 41 Fruits170
  411 Apple100
  412 Banana70
 42 Vegetables90
  421 Lettuce50
  422 Tomatoes40
5 Medicine 

 

Can PowerBI recognize the sum rows while importing the data? Is there an opportunity to mark rows as sum of the others? Or should the data be cleaned before importing to PowerBI?

 

Thanks for your help in advance.

 

Regards

 

Jenny

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-yulgu-msft
Microsoft

Re: Sum rows in dataset

Hi @malynnej ,

 

Power BI won't recognize the total rows automatically while importing the dataset. You may need to transform data. Please have a look at below workaround.

 

Split the Category ID column.

3.PNG   4.PNG

 

Result table.

5.PNG

 

Use a Matrix to display data. Turn off "Stepped layout" option.

2.png 1.png

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
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

4 REPLIES 4
Microsoft v-yulgu-msft
Microsoft

Re: Sum rows in dataset

Hi @malynnej ,

 

There are two columns in source dataset, right? What does each column refer to? 

 

Can PowerBI recognize the sum rows while importing the data? Is there an opportunity to mark rows as sum of the others?

What do you mean "sum rows"? What is your desired output in Power BI?

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
malynnej
Frequent Visitor

Re: Sum rows in dataset

Hi Yuliana,

 

e.g. the category fruits is the sum of banana & apple as well as the category food is the sum of fruits and vegetables. My question is how PowerBI can recognize these rows as totals of the other while importing the dataset. Or if there is an opportunity to mark those rows as totals afterwards.

 

Thanks

 

Jenny

Microsoft v-yulgu-msft
Microsoft

Re: Sum rows in dataset

Hi @malynnej ,

 

Power BI won't recognize the total rows automatically while importing the dataset. You may need to transform data. Please have a look at below workaround.

 

Split the Category ID column.

3.PNG   4.PNG

 

Result table.

5.PNG

 

Use a Matrix to display data. Turn off "Stepped layout" option.

2.png 1.png

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
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

malynnej
Frequent Visitor

Re: Sum rows in dataset

Oh okay, too bad that this is not possible. But thank you for your help and solution! 🙂

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors