Sum rows in dataset

[Anonymous]

Hello,

I have an Excel-dataset which looks like this:

4 Food	260
41 Fruits	170
411 Apple	100
412 Banana	70
42 Vegetables	90
421 Lettuce	50
422 Tomatoes	40
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

[v-yulgu-msft]

Hi @Anonymous ,

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

[Anonymous]

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

[v-yulgu-msft · Accepted solution]

Hi @Anonymous ,

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.

   

Result table.

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

 

Best regards,

Yuliana Gu

[Anonymous]

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