The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hello,
I have a matrix visual where the rows are field parameter values, which come from product attributes in a sales table. This allows the user to select the attributes in a separate slicer to create their own hierarchy. The matrix columns are aggregation measures, such as total sales. This is working well, but I had a request to add penetration, so the user can see a subcategory's share of its parent category. I've read online many ways to do this for static hierarchies, but not for dynamic ones.
These two screenshots show my attempts to create a penetration measure for the sample Sales & Returns dataset. As you can see, the measure works when the user creates a Category -> Price hierarchy, but not if they select Price -> Category. My confidential data has a dozen product attributes, so I would prefer a general solution for all possible hierarchies.
Category - Price Matrix
Price - Category Matrix
You can download the sample data with my measures here.
link requires login - please check.