Calculate parent value on leaf level from a fact table with different granularity levels

Question

I'm not sure if the data model is well suitable for what I want to compute, but I would like first to try if it's possible according to the dataset format provided.

The dataset provided by a data supplier looks similar to the population world bank dataset you can get on github.

It contains the population at country level but also by region or economy group according the current classification of the world bank (Excel-File on the world bank website).

Although it would be best practice to separate the different grain for a better fact table, in my real case, the fact table contains subsets of the whole dataset at different grain level. We don't purchase each leaves for cost reason.

For the example it look like this list: some country and economy regions are selected and should be computed together.

What I'm looking for is to calculate with DAX (Power Pivot / SSAS 2017 or Power BI) the population ratio of the child compared to the parent level, also if the pivot table contains only the leaf members.

There are several examples on the web, how to compute a hierarchy with DAX. However all I found was with a logical hierarchy and fact table:
- fact table at the lowest grain level
- dimension table with calculated columns to build the hierarchy (PATH(), ...)

I build the hierarchy according the solution from Alberto Ferrari.

Population wrong:=
IF (
    [BrowseDepth] > [MaxNodeDepth];
    BLANK ();
    SUM ( population[Value] )
)

In order to not compute the child level on the parent level, I use these measures:

Population w/o aggregation:=
IF (
    [BrowseDepth] = [MinNodeDepth];
    CALCULATE (
        SUM ( population[Value] );
        FILTER (
            population;
            population[Country Code] = CALCULATE (
                VALUES ('country'[Country code]);
                FILTER (
                    'country';
                    'country'[HierarchyDepth] = MIN ('country'[HierarchyDepth])
                )
            )
        )
    )
)


RatioToParent:=
IF (
    ISFILTERED ( country[Level2] )
        && NOT ( ISFILTERED ( country[Level3] ) );
    [Population w/o aggregation]
        / CALCULATE ( [Population w/o aggregation]; ALL ( country[Level2] ) );
    IF (
        ISFILTERED ( country[Level3] ) && NOT ( ISFILTERED ( country[Level4] ) );
        [Population w/o aggregation]
            / CALCULATE ( [Population w/o aggregation]; ALL ( country[Level3] ) );
        IF (
            ISFILTERED ( country[Level4] );
            [Population w/o aggregation]
                / CALCULATE ( [Population w/o aggregation]; ALL ( country[Level4] ) )
        )
    )
)

As long as I use the hierarchy on the rows of the pivot table, I can compute the ratio as wanted:

For instance the countries in the middle income group represents 75% of the world population (in 2010), the lower middle income represents 53% of this group.

However if I just take the country field and not the hierarchy, then the above measure doesn't calculate any value (due probably to the [BrowseDepht] and [NodeDepth] measure).

I can't find a way how to bring at the child level the population value of the parent, since the value is not a SUM of all childs. Without this value, the ratio cannot be computed.

Any clue would be much appreciated.

The ability to compute it outside the hiearchy give visualization possibility (slice some country on a chart for instance).

visu-l visu-l · Accepted Answer · 2019-02-27T11:28:59

I found now a partial solution in order to replace this initial measure:

RatioToParent:=IF (
    ISFILTERED ( country[Level2] )
        && NOT ( ISFILTERED ( country[Level3] ) );
    [Population w/o aggregation]
        / CALCULATE ( [Population w/o aggregation]; ALL ( country[Level2] ) );
    IF (
        ISFILTERED ( country[Level3] ) && NOT ( ISFILTERED ( country[Level4] ) );
        [Population w/o aggregation]
            / CALCULATE ( [Population w/o aggregation]; ALL ( country[Level3] ) );
        IF (
            ISFILTERED ( country[Level4] );
            [Population w/o aggregation]
                / CALCULATE ( [Population w/o aggregation]; ALL ( country[Level4] ) )
        )
    )
)

Without using the ISFILTERED function I got this measure:

RatioToParent 2:=IF (
    HASONEVALUE ( country[Country code] );
    DIVIDE (
        SUM ( population[Value] );
        IF (
            VALUES ( country[Level] ) = 1;
            BLANK ();
            IF (
                VALUES ( country[Level] ) = 2;
                CALCULATE (
                    SUM ( population[Value] );
                    VALUES ( country[Level1] );
                    FILTER ( ALL ( country ); country[Level] = 1 )
                );
                IF (
                    VALUES ( country[Level] ) = 3;
                    CALCULATE (
                        SUM ( population[Value] );
                        VALUES ( country[Level2] );
                        FILTER ( ALL ( country ); country[Level] = 2 )
                    );
                    IF (
                        VALUES ( country[Level] ) = 4;
                        CALCULATE (
                            SUM ( population[Value] );
                            VALUES ( country[Level3] );
                            FILTER ( ALL ( country ); country[Level] = 3 )
                        )
                    )
                )
            )
        )
    )
)

However this measure is not portable yet. If putted in the initial hierarchy, then levels with child won't be calculated (due to the HASONEVALUE function, which is necessary to prevent a DAX error).

May be has someone a clue how to make the last measure "RatioToParent 2" portable, without the HASONEVALUE function.

Calculate parent value on leaf level from a fact table with different granularity levels

1 Answers