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
sm_accordion
Frequent Visitor

Hiding totals for top level hierarch

I have the following matrix in Power BI Desktop with Division and SKU on the Rows and Sales on the Values:

test2.PNG

I have hidden the Division subtotals.  When I collapse by Division, I get the following:

test3.PNG

Is there a way I can suppress the Division totals in this view as well?  I want to just show the three divisions with blanks next to the labels.  I can't really use HASONEVALUE in a measure on the SKU column as Division-3 only has one SKU.  I can't use HASONEVALUE on the Division column as it will suppress the values in the expanded view (first picture above) which I don't want.

 

Any ideas would be appreciated.

1 ACCEPTED SOLUTION
JarroVGIT
Resident Rockstar
Resident Rockstar

You can use HASONEFILTER like this:

 

Sales1 = IF(HASONEFILTER('Table'[SKU]), AVERAGE('Table'[Sales]), "")

 

With this result:

image.pngimage.png

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
JarroVGIT
Resident Rockstar
Resident Rockstar

You can use HASONEFILTER like this:

 

Sales1 = IF(HASONEFILTER('Table'[SKU]), AVERAGE('Table'[Sales]), "")

 

With this result:

image.pngimage.png

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Brilliant, it works like a charm.  Thanks a lot!  Nice trick to use "" instead of Blank().

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.