Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Noob78
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.
Thanks in advance!
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!

Please help!

9 REPLIES 9
BhaveshPatel
Community Champion
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.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

Thank you for this answer.

 

My complete table looks like below:

CountryRegionZoneDealerSalesVolume
France4110011
France4210028
France43100314
France1410041
France1510055
France1610063
France17100719
France2810083
France2910091

 

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 128
10041
10055
10063
100719
Total Region 24
Total Region 423
Total France55

 

or develop

 Somme de SalesVolume
Total Region 128
10041
10055
10063
100719
Total Region 24
10083
10091
Total Region 423
10011
10028
100314
Total France55

 

 

I don't know if it is clear 🙂

 

Thanks!

Noob78
Frequent Visitor

No one?

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])


1.png

 

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.

Capture1.PNG

Best Regards,
Angelia

 

 

Thank you! Shall try and let you know Smiley Very Happy

 

@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 😕

 

 

Hi @Noob78.

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

Capture1.PNG


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

@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

Hi @Noob78,

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

Best Regards,
Angelia

@v-huizhn-msft Thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.