Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Sum rows in dataset

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 ...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

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