summing up at higher granularity?

[EricM]

I have two tables as below

"LevelsTable" is a kind of dimension with a hierachy of 3 levels: Top, Mid and Low

"PotentialTable" contains a number that relates to the second level in the hierarchy.

So "PotentialTable" is a sort of fact table but at a higher granularity than the dimension. A bit similar to a budget table, although there is no logical way to spread the "Potential" at the Low Level.

I would like to create a measure that reports the sum of "Potential" at either Top or Mid Level and returns Blank() at Low level.

I can get this to work by using a Bi-Directional relation and RELATEDTABLE() but I need it for a PowerPivot 2012 context, hence no bi-directional relationship.

I tried to do something like the "Budget Pattern" (Marco Russo, Alberto Ferrari) but I bump into the lack of relationship in the "right" direction.

DEFINE
    MEASURE MeasuresTable[IsPotentialValid] =
        NOT ( ISFILTERED ( LevelsTable[LowLevel] ) )
    MEASURE MeasuresTable[PotentialTotal] =
        CALCULATE (
            SUM ( PotentialTable[Potential] ),
            FILTER (
                ALL ( PotentialTable ),
                CONTAINS (
                    VALUES ( PotentialTable[MidLevel] ),
                    LevelsTable[MidLevel], PotentialTable[MidLevel]
                )
            )
        )

I excluded the "Evaluate" part

This gives me the error "The Column 'LevelsTable[MidLevel]' either does not exist or does not have a relationship to any table in the current context...

How can I filter my "PotentialTable" with MidLevel values filtered on the LevelsTable table?

I know there are other difficulties... but one thing at a time!

Thanks 

Eric

[EricM · Accepted solution]

If anyone is interested... I was looking at it the wrong way round.

One answer is to SUM on the higher granularity table, checking for each row whether there is any record in the (lower level) related table. 

I also added a check to make sure we don't query from the lowLevel, which does not make sense in my sample problem.

It seems to do what I want 

a first measure: 

IsLowLevelFiltered = ISFILTERED ( LevelsTable[LowLevel] )

and the final one: 

SumOfPotential = IF (
            [IsLowLevelFiltered] = FALSE (),
            CALCULATE (
                SUM ( PotentialTable[Potential] ),
                FILTER (
                    ALL ( PotentialTable ),
                    COUNTROWS ( RELATEDTABLE ( LevelsTable ) ) > 0
                )
            ),
            BLANK()
        ) 

[Bitwize_PowerBI]

Hi,

You have a 1 to many relationship between your fact table and your dimension table.  This can only mean that you have only one 'potential' for each 'midlevel' in your dimension (normally, a dimension table is on the 1 side, and the fact table is on the many side).

Your model will only work as long as you have only one 'potential' for each 'midlevel'.  If this is the case, just merge your tables and put your 'potential' in your levels table.  Summing will become much easier as you won't even need measures.

regards,

Dries

[EricM]

Yes, this is what got me confused.

I have many rows in my "dimension" table for only one row inthe "fact" table, which is kind of the wrong way round.

But I can't join as you say because I would end up summing the Mid Level potential for each Low Level row, hence more than once.

If the relation was the right way round, the typical solution would probably be to use RELATEDTABLE(factTable) and SUM but I can't in this case because the granularity difference is the wrong way round.

My tables are not big so I don't know whether the solution I found is efficient, but it seems to work.

In essence, I scan the "fact" table and check, for each row, that I found at least one row in the dimension table.

This avoids double counting...