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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors