cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
v_krishna Member
Member

Table visual - group by

Hi,

Based on below Sample Table Data, I have tried and able to display the data in Table visual.  Refer Image 1

          

RegionLocationItemVolume
Region1Location4Item22968
Region1Location4Item244
Region1Location4Item1163
Region1Location4Item144
Region1Location4Item71043
Region1Location4Item755
Region1Location2Item420190
Region1Location2Item444
Region1Location3Item2127
Region1Location3Item244
Region1Location3Item10
Region1Location3Item144
Region1Location3Item973
Region1Location3Item944
Region1Location3Item4222
Region1Location3Item444
Region1Location3Item3389
Region1Location3Item344
Region1Location3Item786
Region1Location3Item744
Region1Location3Item60
Region1Location3Item644
Region1Location3Item81085
Region1Location3Item844
Region1Location1Item924
Region1Location1Item944
Region1Location1Item896
Region1Location1Item844
Region1Location5Item91
Region1Location5Item955
Region1Location5Item69
Region1Location5Item830
Region1Location5Item855
Region1Location7Item246
Region1Location7Item255
Region1Location7Item925
Region1Location7Item966
Region1Location7Item4112
Region1Location7Item455
Region1Location7Item3139
Region1Location7Item355
Region1Location7Item763
Region1Location7Item755
Region1Location7Item8431
Region1Location7Item866
Region1Location7Item5NULL
Region1Location6Item44373
Region1Location6Item455
Region2Location12Item4358
Region2Location12Item4NULL
Region2Location10Item5NULL
Region2Location10Item5NULL
Region2Location9Item4581
Region2Location9Item4NULL
Region2Location11Item41270
Region2Location11Item4NULL
Region2Location8Item2NULL
Region2Location8Item2NULL

 

 
i have applied Conditional formatting to set background color to the column values.


Below are steps and Refer Image 1:

 

1. Used Table visual. 

 

2. Created below columns (using New Column option) in RegionBreakdown table.
 
 VolumeCheck = if(((RegionBreakdown[Volume]=0) || (RegionBreakdown[Volume]=BLANK())),BLANK(), RegionBreakdown[Volume])
 
 Item1 = If(RegionBreakdown[ItemName] = "Item1", RegionBreakdown[VolumeCheck], 0)
 
RegionColor = if( MAX( RegionBreakdown[RegionName] )="Region1", 1 ,2 )
 
 
3.  Table Fields  -  dragged RegionName, LocationName, Item1  to the Values.
 
4.  on RegionName Value -  i have applied Conditional formatting to set background color to the  RegionName column values using   RegionColor  condition.
       

Image 1

 

TableVisualCapture.PNG

 

Need help on the below Requirement.

Requirement:   Can we groupby RegionName column value in Table visual something like below as in Image 2  ? 

  

Image 2 - Expected behavior

 

 

groupbyCapture.PNG   

 

Thankyou

 

3 REPLIES 3
Ross73312 Super Contributor
Super Contributor

Re: Table visual - group by

Have you tried setting this as a Matrix instead of a table?

Capture.PNGCapture.PNG

 

Note:  I've turned 'Stepped Layout' off (under Row Headers).  I've not applied most of the stuff in your post, but they are all available.  You should be able to simply select your existing table, click on "Matrix" and then ensure your Region and Location columns are under the "Rows".  You can turn all the totals off as well.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Highlighted
v_krishna Member
Member

Re: Table visual - group by

Hi Ross, 

 

I have tried with Matrix visual initially and was able to get the solution BUT the issue i am facing is - unable to set the background color to RegionName column value. Seems as of now Matrix visual doesnot support the conditional formatting to set the background color to column values. I have submitted an idea in powerbi forums to include this feature. ( https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/36010816-conditional-formatting-t... )

 

Please refer the below Matrix visual for reference.

 RegionMatrixCapture.PNG

 

I have tried a work around:

 

Instead of Matrix visual, i have used Table visual to apply Conditional formatting to set background color to the column values.

 

please provide inputs using Table visual. 

 

Thank you.

Ross73312 Super Contributor
Super Contributor

Re: Table visual - group by

Ahh got ya.  Yeah i forgot the Row and Column headers can't be conditionally formatted.  Now i see the limitation you are facing.

 

I think you might be stuck unless the idea you have raised gets implimented.  Tables don't have the "as above" type empty spacing.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 335 members 3,709 guests
Please welcome our newest community members: