cancel
Showing results for
Did you mean:
Frequent Visitor

## Visualisation or pivot table by groups of data

Hi people!
I am new on the forum and think you guys are making amazing job helping us noobs.
As the subject says, I have a data visualisation issue:
I would need a table in which I can see in turns: only the total results of a country / the total results of each zone slicing the country / the details of each entity.

It is the simplest action in Excel: grouping rows and developing the group at envy.
In powerby I don't have the trick!

9 REPLIES 9
Community Champion

There are more than one options to achieve this in powerbi. I would suggest you to post sample data and expected outcome.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Frequent Visitor

My complete table looks like below:

 Country Region Zone Dealer SalesVolume France 4 1 1001 1 France 4 2 1002 8 France 4 3 1003 14 France 1 4 1004 1 France 1 5 1005 5 France 1 6 1006 3 France 1 7 1007 19 France 2 8 1008 3 France 2 9 1009 1

My aim is to give the possibility to each region to filter as below, and toy with the information so that it can always have a look at the National Total, the Regional Total, etc. :

 Somme de SalesVolume Total Region 1 28 1004 1 1005 5 1006 3 1007 19 Total Region 2 4 Total Region 4 23 Total France 55

or develop

 Somme de SalesVolume Total Region 1 28 1004 1 1005 5 1006 3 1007 19 Total Region 2 4 1008 3 1009 1 Total Region 4 23 1001 1 1002 8 1003 14 Total France 55

I don't know if it is clear 🙂

Thanks!

Frequent Visitor

No one?

Microsoft

Hi @Noob78,

For one thing, please create a measure using the following formula. Then create a matrix in PowerBI desktop. Select the Region, Dealer as Row level, Measure1 as value level. You will get the result below.

`Measure1 = SUM(Table4[SalesVolume])`

For another, if you create same measure, create a pivot table, select the Region and Dealer as Row level, the measure as Values, you will get the expected better result as follows.

Best Regards,
Angelia

Frequent Visitor

Thank you! Shall try and let you know

@v-huizhn-msft

I can easily obtain the first screenshot of PowerBi.

However, does the second screenshot not come from Excel? Indeed, I wish to have exactly this visualization, but in Power Bi (I mean, collapsible, etc.).

Correct me if I did not get something right 😕

Microsoft

Hi @Noob78.

The secondshot is a pivot table. First import the data into power pivot model, create a measure as follows.

Then create a pivot table, when you select the Dealer after Region field in Rows level, the Dealer is subcategory automatically. For example, there several Dealer values for Region1. While in Power BI desktop, it display as the first screenshot. And we can’t set the custom subtotal.

Best Regards,
Angelia

Frequent Visitor

@v-huizhn-msft

The visual result is not exactly as I wished, however I note that there are no additional solution for the time being.

Thank you!

Regards

Microsoft

Hi @Noob78,

I reproduce your scenario on my local computer, I will post the update if I find new solution.

Best Regards,
Angelia

Frequent Visitor

@v-huizhn-msft Thanks!

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors