Macro to Collapse pivot rows which don't have subrows?
Hi there,
I've got a 3-level hierachy (Customer - Category - Product). Could someone please help to write a macro which will collapse any Categories which don't have Products (as highlighted in yellow below)?
I think best explained by the screenshot. Sample book attached. I'd really appreciate any help. I've tried for a good two hours in order to do this but I'm chasing my tail.
screenshot.png
Best wishes,
Michelle
The problem with collapsing the Category Row Field, is that it will collapse (or not) that field for all Customers.
In my attachment, I've added a couple of rows which demonstrate this problem - try collapsing Category B for Customer 1, and you hide the valid products for Customer 2...
It would be neater to load your Table data to the data model, then create an explicit DAX measure for the Sum Qty, which doesn't display individual blank product values:
Please Login or Register to view this content.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks