Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

summing up at higher granularity?

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 hierar...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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 :manwink:

    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()
        )
Anonymous's avatar
Anonymous
Not applicable
9 years ago

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 :manwink:

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's avatar
    Bitwize_PowerBI
    Advocate IV
    9 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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...