Matrix with category and sub-category with specific formatting requirements

[Anonymous]

Hi,

I'm trying to create a matrix that summaries sales for country and region within the country. Some countries have many regions (states in the USA, for example) and some countries have no regions. When I use the matrix to display the data I get the following:

Country1 <country sum>

   Region1 <region within country 1 sum>

   Region3 <region within country 1 sum>

   Region3 <region within country 1 sum>

Country 2 <country2 sum>

  <blank> <country2 sum>

Country 3...

I would like to remove null regions so the data looks like this:

Country1 <country sum>

   Region1 <region within country 1 sum>

   Region3 <region within country 1 sum>

   Region3 <region within country 1 sum>

Country 2 <country2 sum>

Country 3...

Is this possible?

[v-jiascu-msft]

Hi @Anonymous,

The @ImkeF's solution works. Please try it out. You also can try the new feature of the November release. Please refer to the snapshot below.

Best Regards,
Dale

[Anonymous]

Dale, would it be possible to not expand in the first place? The sub-category field is null.

[mwegener]

No, this is not possible.

[ImkeF]

Hm - not sure if I got the request right, but for my understanding a measure like this should work:

RaggedH1 = 
IF (
    ISFILTERED ( DimRegion[Region] ) = FALSE ()
        || MIN ( DimRegion[Region] ) <> "";
    SUM ( 'Fact'[Amount] );
    BLANK ()
)

[Anonymous]

@ImkeF: this didn't work, unfortunately. It creates a blank row. The user requires that the row not shown at all (only country level results shown when the region column is null).

[ImkeF · Accepted solution]

Please check out enclosed file and state clearly what your desired result is instead, as I still don't understand what your exact requiremetn is.

[Anonymous]

Hi Imke,

I had to reorganize my data to match your setup and once I did your solution worked. Thanks for your help!

Regards,

Mustafa