Forum Discussion
Remove filter in If statement
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.
Unfortunately this still does not work.
The initial filter on Level 06 overrules the selections I want to apply on Level 10. So I do see the the 'fixed costs' but not the 'some fixed costs'.
The problem remains the same. I understand the difficulty since I filter on two different levels so the real trick is to undo a the filter on level 6 and then reapply a 'clean' filter on level 10, within the same column basically.
Sander800,
Could you please share sample data of your table? Which column do you use to calculate 500 and 20?
Regards,
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 ID SCOA Level07 ID SCOA Level08 ID SCOA Level09 ID SCOA Level10 ID Amount 4500000000 4510000000 4511000000 4511100000 64405401 20 4500000000 4510000000 4511000000 4511100000 64405402 25 4500000000 4510000000 4511000000 4511100000 64405403 30 4500000000 4510000000 4511000000 4511200000 64405404 10 4500000000 4510000000 4511000000 4511200000 64405405 5 4500000000 4510000000 4511000000 4511200000 64405406 100 4500000000 4510000000 4512000000 4512100000 64405407 45 4500000000 4510000000 4512000000 4512100000 64405408 70 4500000000 4510000000 4512000000 4512100000 64405409 70 4500000000 4510000000 4512000000 4512200000 64405410 30 4500000000 4520000000 4521000000 4521100000 64405411 30 4500000000 4520000000 4521000000 4521100000 64405412 30 4500000000 4520000000 4521000000 4521100000 64405413 10 4500000000 4520000000 4521000000 4521100000 64405414 10 4500000000 4520000000 4521000000 4521200000 64405415 15 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,
LydiaHi 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?