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 test this feature, visit 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 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.