For hierarchy LEVEL1 and LEVEL2, leave specific/defined items blank instead of using subtotals

[hruv01]

Hi, I have a CSV file containing a hierarchy for a matrix table in Power BI. The hierarchy is structured in such a way that the first column represents the first level of the hierarchy, the second column represents the second level, and so on up to the fifth level, which corresponds to the fifth column. This hierarchy is linked to a data table through a key, where the numerical values are obtained. The matrix table assigns values to the lowest level of the hierarchy, while higher levels simply aggregate the values of the lower levels. What I need is for the first level of the hierarchy, called "Total," to not aggregate values but remain blank. How can I specify this particular level without DAX returning blank values on lower levels? I'm familiar with the special button for removing subtotal rows, but that doesn't solve my problem when I also need to define specific items from level 2 too.
Thank youu

[Sahir_Maharaj]

Hello @hruv01,

To achieve your desired result, you can use a combination of DAX functions and settings within the matrix visual.

1. Create a relationship between the two tables using the appropriate key column.
2. Add a matrix visual to your report canvas.
3. Place the hierarchy columns in the rows section of the matrix, from the highest level to the lowest level (e.g., Level 1 in the first column, Level 2 in the second column, etc.).
4. Place the numerical values column from the data table in the values section of the matrix.
5. Use this following DAX formula to define a measure that calculates the values for each level but keeps the "Total" level blank.

MyMeasure =
IF (
    ISFILTERED ( 'HierarchyTable'[Level1Column] ),
    BLANK (),
    SUM ( 'DataTable'[NumericValue] )
)

Note: Go back to the matrix visual and replace the existing numerical values column with the newly created measure. To remove the subtotals for the first level of the hierarchy, select the matrix visual, go to the "Format" tab, expand the "Subtotals" section, and disable the "Row subtotals" option.

Should you require further details or assistance please do not hesitate to reach out to me.