Multi Tier Allocation from multiple tables

[JustDavid]

I do not know if what I desired to achieve is achievable. Been trying this for a couple of days and unable to produce result.

I have 4 sources - 3 allocations tables (T1, T2 and T3 allocation) and 1 fact Tables.

Below is the screenshot of how the source was originally (and for explanation of my logic) as well as my desired end result.

Note that, in the sample pbix file, I have made it into columnar.

A quick explanation of how the allocation works:

The IDs across the columns are where it's coming from and the IDs down the rows are where it's going to.

E.g1. Looking at T1 table, all of the amount that's in ln9000 goes to cd9101 at 100%. However, nothing is coming to cd9102.

E.g2. Looking at T2 table, all of the amount that's in cd9101 goes to ld9121 and ld9122 at 25% and 75% respectively.

The allocation needs to come from T1, T2 and finally T3.

In other words, all items that are down the rows of T3 (in this example, fn9401 and fn9402) needs to receive the allocation from it's predecessors.

There are 2 "views" of My desired result - column G to J  and  column L to O.

The first view (column G to J) is easily achive. The difficult part is the 2nd view (column L to O) where I need to show all of the ids that's in Tier1, Tier 2 and Tier 3 all flows down to 'FN' IDs (note that in real data, the 'FN' IDs are the IDs that are items that are down the row of Tier3, and they are not indicated by 'FN').

The reason why I say it's difficult is because of 2 issues (that I can think of):

1st issue:

As you can see in my 1st desired result (column G to J), there are missing [obj code] and [cost code] in each [id]. i.e ln9000 has [obj code] 'a' and [cost code] '101' and '102' and these are not appearing in the rest of [id]. Thus how can I make power bi know that it needs to allocate x% but where it's going to is missing [obj code] and [cost code]?

The solution that I can think of is to have another table where I can have all [obj code] and [cost code] for each [id], and use this table instead. In my sample pbix file, you can see it as 'mirrorFactTableWithAllCostCodes'.

2nd issue:

The allocation is 1 to many relationship, and I need  power bi to know and calculate the full amount first before it can start allocating it down.

i.e. in T3 Allocation, fn9401 receive amount from ld9121, ld9122 and ld9123 at 50%, 25% and 75% respectively. However, ld9121 receive 25% from cd9101 (coming from T2), and cd9101 receive 100% from ln9000 (coming from T1). Thus power bi needs to know and calculate each inidivual IDs it is receiveing from.

Hopefully you know what I am trying to achieve and the road block that I'm facing.

Below is the logic on how to get the an amount down to the bottom:

fn9401 receive from ld9121 (50%), ld9122 (25%) and ld9123 (75%).

            ld9121 receive from cd9101 (25%)

                        cd9101 receive from ln9000 (100%) and cs9025 (100%)

            ld9122 receive from cd9101 (25%) and cd9102 (25%)

                        cd9101 receive from ln9000 (100%) and cs9025 (100%)

                        cd9102 receive nothing

            ld9123 receive from cd9102 (75%)

                        cd9102 receive nothing

To put in numbers, looking at [obj code] = 'a' and [cost code] = '101' (remember that my 2nd desired result, it needs to inherit any missing [obj code] and [cost code])

fn9401                               = 0    (note that it does not have [obj code = 'a'  and cost code = '101'])

fn9401 inherit from 9121   = 45 (this is coming from ld9121 distribute) * 50% (inherit from ld9121 @ 50%)

                                           = 22.50

fn9401 inherit from 9122   = 135 (this is coming from ld9122 distribute) * 25% (inherit from ld9122 @ 25%)

                                           = 33.75

fn9401 inherit from 9123   = 0 (this is coming from ld9123 distribute) * 75% (inherit from ld9122 @ 75%)

                                           = 0

fn9401 final                        = 0 + 22.50 + 33.75 + 0 = 56.25

ln9000                  = 180

ln9000 inherit       = 0    (note that ln9000 do not inherit from any IDs)

ln9000 distribute  = 180 + 0 = 180

cs9025                  = 0     (note that it does not have [obj code = 'a'  and cost code = '101'])

cs9025 inherit       = 0    (note that cs9025 do not inherit from any IDs)

cs9025 distribute  = 0 + 0 = 0

cd9101                                 = 0    (note that it does not have [obj code = 'a'  and cost code = '101'])

cd9101 inherit from ln9000  = 180 * 100% = 180   (inherit from ln9000 @ 100%)

cd9101 inherit from cs9025  = 0 * 100% = 0   (inherit from cs9025 @ 100%)

cd9101 distribute  = 0 + 180 = 180

cd9102                                 = 0    (note that it does not have [obj code = 'a'  and cost code = '101'])

cd9102 inherit from ln9000  = 180 * 0% = 0   (inherit from ln9000 @ 0%)

cd9102 inherit from cs9025  = 0 * 0% = 0   (inherit from cs9025 @ 0%)

cd9102 distribute  = 0 + 0= 0

ld9121                                  = 0    (note that it does not have [obj code = 'a'  and cost code = '101'])

ld9121 inherit from cd9101 = 180 * 25% = 45   (inherit from cd9101 @ 25%)

ld9121 inherit from cd9102 = 0 * 0% = 0   (inherit from cd9102 @ 0%)

ld9121 distribute  = 0 + 45 = 45

ld9122                                 = 0    (note that it does not have [obj code = 'a'  and cost code = '101'])

ld9122 inherit from cd9101 = 180 * 75% = 135   (inherit from cd9101 @ 75%)

ld9122 inherit from cd9102 = 0 * 25% = 0   (inherit from cd9102 @ 25%)

ld9122 distribute                  = 0 + 135 = 135

ld9123                                 = 0    (note that it does not have [obj code = 'a'  and cost code = '101'])

ld9123 inherit from cd9101 = 180 * 0% = 0   (inherit from cd9101 @ 0%)

ld9123 inherit from cd9102 = 0 * 75% = 0   (inherit from cd9102 @ 75%)

ld9123 distribute                  = 0 + 0 = 0

Sample PBIX File 

[Anonymous · Accepted solution]

Hi @JustDavid ,

Please follow these steps:

1.Use the following DAX expression to create a table

Table = 
FILTER (
    SELECTCOLUMNS (
        mirrorFactTableWithAllCostCodes,
        "id", [id],
        "obj code", [obj code],
        "cost code", [cost code]
    ),
    [id] = "fn9401"
        || [id] = "fn9402"
)

2.Use the following DAX expression to create a column in table 'mirrorFactTableWithAllCostCodes'

Column = 
VAR _result = SUMX(RELATEDTABLE(factTable),[Budget])
RETURN IF(ISBLANK(_result),0,_result)

3.Use the following DAX expression to create a measure

MEASURE =
VAR _obj =
    SELECTEDVALUE ( 'Table'[obj code] )
VAR _cost =
    SELECTEDVALUE ( 'Table'[cost code] )
VAR _id =
    SELECTEDVALUE ( 'Table'[id] )
VAR _table1 =
    ADDCOLUMNS (
        'tbl_Tier1',
        "c1",
            SUMX (
                FILTER (
                    mirrorFactTableWithAllCostCodes,
                    [id] = EARLIER ( tbl_Tier1[T1_From] )
                        && [obj code] = _obj
                        && [cost code] = _cost
                ),
                [Column]
            ) * [T1_Alloc]
    )
VAR _table2 =
    ADDCOLUMNS (
        'tbl_Tier2',
        "c1",
            VAR _result =
                (
                    SUMX ( FILTER ( _table1, [T1_To] = EARLIER ( tbl_Tier2[T2_From] ) ), [c1] )
                        + SUMX (
                            FILTER (
                                'mirrorFactTableWithAllCostCodes',
                                [id] = EARLIER ( tbl_Tier2[T2_From] )
                                    && [obj code] = _obj
                                    && [cost code] = _cost
                            ),
                            [Column]
                        )
                ) * [T2_Alloc]
            RETURN
                IF ( ISBLANK ( _result ), 0, _result )
    )
VAR _table3 =
    ADDCOLUMNS (
        'tbl_Tier3',
        "c1",
            VAR _result =
                (
                    SUMX ( FILTER ( _table2, [T2_To] = EARLIER ( tbl_Tier3[T3_From] ) ), [c1] )
                        + SUMX (
                            FILTER (
                                'mirrorFactTableWithAllCostCodes',
                                [id] = EARLIER ( tbl_Tier3[T3_From] )
                                    && [obj code] = _obj
                                    && [cost code] = _cost
                            ),
                            [Column]
                        )
                ) * [T3_Alloc]
            RETURN
                IF ( ISBLANK ( _result ), 0, _result )
    )
RETURN
    SUMX ( FILTER ( _table3, [T3_To] = _id ), [c1] )
        + SUMX (
            FILTER (
                'mirrorFactTableWithAllCostCodes',
                [id] = _id
                    && [cost code] = _cost
                    && [obj code] = _obj
            ),
            [Column]
        )

4.Final output

Best Regards

[JustDavid]

@Anonymous , 
Thank you for your help.

A few questions for you.

1. On your 1st point where you use DAX to create a table, in the formula you've manually identified the ids (screenshot below) 

   [id] = "fn9401"
           || [id] = "fn9402"​

   If i want to make this dynamic, how can I do this? (perhaps I create this table via PowerQuery)
   
   
2. I realized that you're able to achieve my 2nd view desired result via using this table that you've created via DAX, why can't I use the existing tables that I had?
   
   
3. In your final output, when I change it from 'Table' to 'Matrix', the total in each of those ids and Grand Total are showing 0. Why is that? 
   
   
4. Assume if your way is the only way that I'd like to achive my result, looking at your 2nd formula which create a column in table 'mirrorFactTableWithAllCostCodes' 

   Column = 
   VAR _result = SUMX(RELATEDTABLE(factTable),[Budget])
   RETURN IF(ISBLANK(_result),0,_result)

   if I had millions of rows, would PQ be much better in doing the lookup?

[Anonymous]

Hi @JustDavid ,

1. Try this, it should change dynamically based on the value in the field "tbl_Tier3".

FILTER (
    SELECTCOLUMNS (
        mirrorFactTableWithAllCostCodes,
        "id", [id],
        "obj code", [obj code],
        "cost code", [cost code]
    ),
    [id] IN VALUES ( 'tbl_Tier3'[T3_To] )
)

2.

why can't I use the existing tables that I had?

Do you mean table 'mirrorFactTableWithAllCostCodes'?

It should work fine as well, but you'll need to modify the DAX expression to create a virtual table to replace this step. I created the new table just to facilitate my thought process.

3.The only filter conditions here are 'id', 'obj code', which corresponds to multiple 'cost code'.' selectedvalue' function returns the result only if it is a unique value. Otherwise the value returned is blank

4. I think it would be more efficient in PQ

Comparing DAX calculated columns with Power Query computed columns - SQLBI

[JustDavid]

@Anonymous  Thank you for your reply and answering my questions.

I have yet to apply the changes on the 1st and 2nd question, as I'm trying to replicate your thought process in PQ but to no avail (yes been trying this for a good 2 weeks).

Am wondering if I'm going to use your method, but instead of creating a calculated table, but using my exisiting 'mirrorFactTableWithAllCostCodes', how am I going to do it?

Back to question 3, is there a way to have those automatically SUM rather than showing 0? This is what I was trying to accomplish for the past 2 weeks so that everything all flows.

Although I'm not new to DAX, but my skill is still a newbie. Been trying to figure out your formula that ADDCOLUMN, SUMX, FILTER and EARLIER and no the column return as 0. So can't really visualize what you're doing and what's the output is.