Power BI Matrix: Dynamic Percentage Calculation Across Hierarchical Levels Using Field Parameters

[Ritaf1983]

Hi 🙂

I have a matrix visual analyzing employee costs based on three dimensions:

• Site
• Department
• Gender

Each dimension is selected dynamically using Field Parameters.

My goal is to display, at each hierarchical level, the relative percentage of each row compared to the total of the opened hierarchy level. The expected behavior is demonstrated in the attached screenshot.

The challenge arises because the order of selection can change.

• Using a standard ISINSCOPE approach works only in one direction.
• If a user drills down in an order different from my predefined conditions, the percentage calculation breaks.
  
  

I attempted to concatenate the selected dimensions to determine the hierarchy dynamically, but the issue is that the engine does not order the concatenated string based on the selection order—it orders it based on the sorting logic of the Parameter Table.

 

Is there a DAX-based solution to dynamically adjust the calculation based on the actual drill-down path chosen by the user?

The pbix with the dummy data is attached

Any insights would be greatly appreciated

[V-yubandi-msft]

Hi @Ritaf1983 ,

We noticed we haven't received a response from you yet, so we wanted to follow up and ensure the solution we provided addressed your issue. If you require any further assistance or have additional questions, please let us know.

Your feedback is valuable to us, and we look forward to hearing from you soon.

[V-yubandi-msft]

Hi @Ritaf1983 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[V-yubandi-msft]

Hi @Ritaf1983 ,

@Deku , provided a good  response that meets your requirements ?  If you need any additional details or clarification, please feel free to reach out.

Is your issue resolved? If so, kindly mark the solution as accepted. This will help others with similar issues find the answer more easily.

[Deku · Accepted solution]

I had a look

selectedParameter = TOJSON( Parameter )

Using this we can see the parameters being applied. The problem is the result of the measure is always in the same order, even when the visual has memory of the order of selection.

If you look at the query you can see in the query that the visual knows the order. But can't see anyway to surface the filter ordering. Had a look at SQL post on Field Parameter, and there is already a comment that confirms