Table visual - group by

[Anonymous]

Hi,

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

Region	Location	Item	Volume
Region1	Location4	Item2	2968
Region1	Location4	Item2	44
Region1	Location4	Item1	163
Region1	Location4	Item1	44
Region1	Location4	Item7	1043
Region1	Location4	Item7	55
Region1	Location2	Item4	20190
Region1	Location2	Item4	44
Region1	Location3	Item2	127
Region1	Location3	Item2	44
Region1	Location3	Item1	0
Region1	Location3	Item1	44
Region1	Location3	Item9	73
Region1	Location3	Item9	44
Region1	Location3	Item4	222
Region1	Location3	Item4	44
Region1	Location3	Item3	389
Region1	Location3	Item3	44
Region1	Location3	Item7	86
Region1	Location3	Item7	44
Region1	Location3	Item6	0
Region1	Location3	Item6	44
Region1	Location3	Item8	1085
Region1	Location3	Item8	44
Region1	Location1	Item9	24
Region1	Location1	Item9	44
Region1	Location1	Item8	96
Region1	Location1	Item8	44
Region1	Location5	Item9	1
Region1	Location5	Item9	55
Region1	Location5	Item6	9
Region1	Location5	Item8	30
Region1	Location5	Item8	55
Region1	Location7	Item2	46
Region1	Location7	Item2	55
Region1	Location7	Item9	25
Region1	Location7	Item9	66
Region1	Location7	Item4	112
Region1	Location7	Item4	55
Region1	Location7	Item3	139
Region1	Location7	Item3	55
Region1	Location7	Item7	63
Region1	Location7	Item7	55
Region1	Location7	Item8	431
Region1	Location7	Item8	66
Region1	Location7	Item5	NULL
Region1	Location6	Item4	4373
Region1	Location6	Item4	55
Region2	Location12	Item4	358
Region2	Location12	Item4	NULL
Region2	Location10	Item5	NULL
Region2	Location10	Item5	NULL
Region2	Location9	Item4	581
Region2	Location9	Item4	NULL
Region2	Location11	Item4	1270
Region2	Location11	Item4	NULL
Region2	Location8	Item2	NULL
Region2	Location8	Item2	NULL

 
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

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

   

Thankyou

[mahenkj2]

Hi @Anonymous ,

Since you have reached so far, to get most work done with the table visual, I would see potential possibility to make it closer to your desired result (need to check).

If possible, add a running index to Region, so first instance of Region change is 1 else say 2, and then with addition to background formatting, also use Font formatting based on Rule with this running index. Use a matching color to background when second event of region appears. Not sure, if this will be working as you intend but another workaround to test!

[Anonymous]

can we do this now with table visual

[Anonymous]

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

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.

[nickysmix]

THANKS

[Anonymous]

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.

 

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.

[Anonymous]

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.