Forum Discussion

jmmoran20's avatar
jmmoran20
Regular Visitor
6 years ago
Solved

Grouping Table Data by Region

Hello,

 

Looking for insight on grouping/conjoining data in a table. I've attached a picture to better describe what I'm looking to do. 

 

As you can see, for USA there are several sites and it only displays USA once (instead of having USA, USA, USA, USA, etc). How can I do this? Unfortunately, I don't have access to this table just a screenshot of it. 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi  jmmoran20 ,

     

    Value showing may be a problem when using matrix,not sure what's in your data,see my sample below:

    You may take a try,just go to format>row headers>steps layout :off

    If matrix doesnt work,I have a workaround for you:

    First go to query editor>add column>index column;

    Then create 2 columns as below:

    _Area = 
var _previous=CALCULATE(MAX('Table'[Area]),FILTER(ALL('Table'),'Table'[Index]<EARLIER('Table'[Index])))
Return
IF(_previous<>BLANK(),BLANK(),'Table'[Area])
    _Country = 
var _previous=CALCULATE(MAX('Table'[Country]),FILTER('Table','Table'[Index]<EARLIER('Table'[Index])&&'Table'[Country]=EARLIER('Table'[Country])))
Return
IF(_previous<>BLANK(),BLANK(),'Table'[Country])

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi jmmoran20 

     

    Not sure which chart you are using for visualization. Check the format tab of the vizualization if any avaiable option is present.

     

    Alternatively, I would use the matrix chart for visualization, this will automatically repeat the names you have mentioned.

     

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

    Regards,
    Pranit

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    6 years ago

    Hi  jmmoran20 ,

     

    Value showing may be a problem when using matrix,not sure what's in your data,see my sample below:

    You may take a try,just go to format>row headers>steps layout :off

    If matrix doesnt work,I have a workaround for you:

    First go to query editor>add column>index column;

    Then create 2 columns as below:

    _Area = 
var _previous=CALCULATE(MAX('Table'[Area]),FILTER(ALL('Table'),'Table'[Index]<EARLIER('Table'[Index])))
Return
IF(_previous<>BLANK(),BLANK(),'Table'[Area])
    _Country = 
var _previous=CALCULATE(MAX('Table'[Country]),FILTER('Table','Table'[Index]<EARLIER('Table'[Index])&&'Table'[Country]=EARLIER('Table'[Country])))
Return
IF(_previous<>BLANK(),BLANK(),'Table'[Country])

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!