Create hierarchy without summation

Wendeley-North You need to create a measure using the ISINSCOPE function to see where you are in the hierarchy when you drill down and based on where you are in the hierarchy, have your measure just aggregate those records, for example, if you are Factor level then SUM would where Subfactor is blank, something on those groups and that will get you going.

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

After some further googling I got it to work - however, I added an additional column that counts the level of the data (basically counting the number of blanks in each row) such that the raw data now looks like this:

Raw Data

Final Code

Ignore_Aggregate_Val = 
VAR subfactorSUM =
    CALCULATE (
        SUM(Table[Value]),
        Table[Agg Level] = 0,
        ALLEXCEPT ( Table, Table[SubFactor] )
    )
VAR factorSEL =
    ISINSCOPE ( Table[Factor] )
VAR headerSUM =
    CALCULATE (
        SUM(Table[Value]),
        Table[Agg Level] = 2,
        ALLEXCEPT ( Table, Table[Header] )
    )
VAR headerSEL =
    ISINSCOPE ( Table[Header] )

RETURN
    SWITCH(
        TRUE(),
        subfactorSEL, subfactorSUM,
        factorSEL, factorSUM,
        headerSEL, headerSUM
    )