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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.