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 an...
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!
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!