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
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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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