please help with the formula of “% of Parent Row Total”for Multi-level Hierarchy in BI, like we do have in Excel Pivot. I have tried various formulas but none of them works the way Pivot does, for multilevel parent row headers.

top of page

To see this working, head to your live site.

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

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

1 comment

Your Answer (1)

bottom of page

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

Saleswith the following columns:CategorySubCategorySalesAmount2. Create a Hierarchy:In the Fields pane, drag the

Categoryfield above theSubCategoryfield to create a hierarchy. Name this hierarchy asCategoryHierarchy.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

CategoryHierarchyto the Rows shelf of a Matrix visualization.Drag the

SalesAmountand% of Parent Row Totalmeasures to the Values shelf.