Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Matrix with category and sub-category with specific formatting requirements

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?

1 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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

7 REPLIES 7
v-jiascu-msft
Employee
Employee

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.

Matrix-with-category-and-sub-category-with-specific-formatting-requirements

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

No, this is not possible.
Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


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 ()
)

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

@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).

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.