Forum Discussion

Sander800's avatar
Sander800
Helper I
9 years ago
Solved

Remove filter in If statement

Hi All,   Hope you can help me out with this one. I am trying to create 2 sets/ groupings of accounts which I want to show in a table in the rows. However the second set is part of the first set hi...
Need Help
Tutorial Requests
  • robofski's avatar
    robofski
    9 years ago

    How about creating two calculated columns:

     

    Total Costs = IF(PL_Blink_Cube[SCOA Level06 ID] = 4500000000, PL_Blink_Cube[Amount],BLANK())

    Some Costs = IF(PL_Blink_Cube[SCOA Level10 ID] = 64405401, PL_Blink_Cube[Amount],BLANK())

     

    This way you keep the rest of the table and have all your relationships.

Sander800's avatar
Sander800
Helper I
9 years ago

Hi,

 

See the below the table. As you can see there are multiple columns as part of this hierarchy. I want to show in 2 rows the total costs (col SCOA Level06 ID) and in the row below 'some fixed costs' (account 64405401 Col SCOA Level10 ID).

 

Hope this clarifies.

 

Thanks

 

SCOA Level06 IDSCOA Level07 IDSCOA Level08 IDSCOA Level09 IDSCOA Level10 IDAmount
45000000004510000000451100000045111000006440540120
45000000004510000000451100000045111000006440540225
45000000004510000000451100000045111000006440540330
45000000004510000000451100000045112000006440540410
4500000000451000000045110000004511200000644054055
450000000045100000004511000000451120000064405406100
45000000004510000000451200000045121000006440540745
45000000004510000000451200000045121000006440540870
45000000004510000000451200000045121000006440540970
45000000004510000000451200000045122000006440541030
45000000004520000000452100000045211000006440541130
45000000004520000000452100000045211000006440541230
45000000004520000000452100000045211000006440541310
45000000004520000000452100000045211000006440541410
45000000004520000000452100000045212000006440541515
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Sander800,

The only way I can think of is to create a new table using DAX below.

Table 2 = SUMMARIZE(PL_Blink_Cube,"Fixed Expenses",CALCULATE(SUM(PL_Blink_Cube[Amount]),FILTER(PL_Blink_Cube,PL_Blink_Cube[SCOA Level06 ID]=4500000000)),"Some fixed costs",CALCULATE(SUM(PL_Blink_Cube[Amount]),FILTER(PL_Blink_Cube,AND(PL_Blink_Cube[SCOA Level06 ID]=4500000000,PL_Blink_Cube[SCOA Level10 ID]=64405401))))



Regards,
Lydia

  • robofski's avatar
    robofski
    Resolver II
    9 years ago

    How about creating two calculated columns:

     

    Total Costs = IF(PL_Blink_Cube[SCOA Level06 ID] = 4500000000, PL_Blink_Cube[Amount],BLANK())

    Some Costs = IF(PL_Blink_Cube[SCOA Level10 ID] = 64405401, PL_Blink_Cube[Amount],BLANK())

     

    This way you keep the rest of the table and have all your relationships.

  • Sander800's avatar
    Sander800
    Helper I
    9 years ago

    Hi Lydia,

    This really is a big step in the right direction.

    However this way, of course, I loose al my relations. How can I add (the other) dimensions to this new table?

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Sander800,


    What other dimensions do you refer to? Please post expected result here.

    Regards,

    Lydia

  • Sander800's avatar
    Sander800
    Helper I
    9 years ago

    Thanks all,

     

    This really helps me in the right direction. I think this will help me solve me issue.

     

    Again thanks for the help!