please help with the formula of “% of Parent Row Total”for Multi-level Hierarchy in BI, like we do have in Excel Pivot.

Here's a step-by-step guide to create a "% of Parent Row Total" measure for a multi-level hierarchy:

1. Sample Data:

Assuming you have a table named Sales with the following columns:

• Category

• SubCategory

• SalesAmount

2. Create a Hierarchy:

In the Fields pane, drag the Category field above the SubCategory field to create a hierarchy. Name this hierarchy as CategoryHierarchy.

3. Create the Measure:

To calculate the "% of Parent Row Total", you'll need to determine the context of the current row and then calculate the percentage based on the parent's total.

Here's a DAX formula to achieve this:

% of Parent Row Total =

VAR CurrentLevel = SELECTEDVALUE('Sales'[CategoryHierarchy], BLANK())

VAR ParentTotal =

SWITCH(TRUE(),

ISBLANK(CurrentLevel), SUM('Sales'[SalesAmount]), // If top level, take total sales

VALUES('Sales'[Category]), // If subcategory level, take category total

BLANK() // Default

)

RETURN

DIVIDE(SUM('Sales'[SalesAmount]), ParentTotal)

4. Use the Measure in a Matrix Visualization:

• Drag the CategoryHierarchy to the Rows shelf of a Matrix visualization.

• Drag the SalesAmount and % of Parent Row Total measures to the Values shelf.