Split amount between departments

[Anonymous]

Hi

Need help with at DAX measure. Each line in the FACT table contains an amount, a department, and a 'SplitID' column, that specifies among witch departments to split the amount. Ex. The amount for Dep 90-1 in januar is 150/500 * 10.000=3.000. Nb: In many lines in the real FACTTrans, SplitID is null, and the full amount is allocated to the department in column 'Dep'.

Must be possible, but how? 🐵 Regards, Henrik

[Anonymous · Accepted solution]

I will give a step by step solution to this. I suggest you start with a blank Power BI file and start from scratch for better understanding.

Step 1: Add the DIMSplit table to your data model using the "New table" option using the following code.

DIMSplit =
DATATABLE (
    "ID", STRING,
    "Dep", STRING,
    "Share", INTEGER,
    {
        { "aaa", "90-1", "150" },
        { "aaa", "90-2", "150" },
        { "aaa", "90-3", "200" },
        { "bbb", "90-1", "50" },
        { "bbb", "90-2", "50" }
    }
)

Step 2: Add the "FACTTrans" table to your data model using the "New Table" option using the following code.

FACTTrans =
DATATABLE (
    "Date", DATETIME,
    "Amount", DOUBLE,
    "Dep", STRING,
    "SplitID", STRING,
    {
        { "01-01-2020", "10.000", "90-1", "aaa" },
        { "01-01-2020", "15.000", "90-1", "bbb" },
        { "01-01-2020", "25.000", "90-1", BLANK () },
        { "02-02-2020", "20.000", "90-1", "aaa" },
        { "03-03-2020", "30.000", "90-1", "bbb" }
    }
)

Step 3:  Add the "Expected Result" table to your data model using the "New Table" option using the following DAX code.

ExpectedResult =
VAR SumTable =
    SUMMARIZECOLUMNS ( FACTTrans[Date], DIMSplit[Dep], DIMSplit[ID] )
VAR EmptyIDTable =
    GENERATE (
        SUMMARIZECOLUMNS ( 'FACTTrans'[Date], DIMSplit[Dep] ),
        ROW ( "ID", BLANK () )
    )
RETURN
    UNION ( SumTable, EmptyIDTable )

Step 4: Add the following calculated columns to the "Expected Result" table using the following DAX codes.

Amt =
VAR CurrentDate = ExpectedResult[Date]
VAR CurrentDepartment = ExpectedResult[Dep]
VAR CurrentID = ExpectedResult[ID]
RETURN
    SUMX (
        FILTER (
            FACTTrans,
            FACTTrans[Dep] = CurrentDepartment
                && FACTTrans[Date] = CurrentDate
                && FACTTrans[SplitID] = CurrentID
        ),
        FACTTrans[Amount]
    )

DateIDAmount =
VAR CurrentDate = ExpectedResult[Date]
VAR CurrentID = ExpectedResult[ID]
VAR Sum1 =
    SUMX (
        FILTER (
            ExpectedResult,
            ExpectedResult[Date] = CurrentDate
                && ExpectedResult[ID] = CurrentID
        ),
        ExpectedResult[Amt]
    )
VAR Sum2 = ExpectedResult[Amt]
RETURN
    IF ( ISBLANK ( ExpectedResult[ID] ), Sum2, Sum1 )

PercentageSplit =
VAR CurrentID = ExpectedResult[ID]
VAR CurrentDep = ExpectedResult[Dep]
VAR DepIDAmount =
    SUMX (
        FILTER ( DIMSplit, DIMSplit[Dep] = CurrentDep && DIMSplit[ID] = CurrentID ),
        DIMSplit[Share]
    )
VAR IDAmount =
    SUMX ( FILTER ( DIMSplit, DIMSplit[ID] = CurrentID ), DIMSplit[Share] )
RETURN
    IF ( ISBLANK ( ExpectedResult[ID] ), 1, DIVIDE ( DepIDAmount, IDAmount, 0 ) )

FinalAmount =
ExpectedResult[DateIDAmount] * ExpectedResult[PercentageSplit]

Step 5: Add a matrix visual to your report and use the Expected Result table's date(month) and Department fields to column and rows respectively and add the "Final Amount" field to the "Values" section of the matrix which will give you the following result.

This is not an optimum solution, but nevertheless you will get the results and you can also see the intermediate results at every step. Later on, you may simplify the codes or rewrite the same using measures though that will be consuming your CPU power a lot if your dataset is quite large. 

Please let me know if you have any trouble understanding any of the DAX code snippets. By the way, there are no relationships between any of these tables.

[camargos88]

Hi @Anonymous 

I have create this file as an example: Download PBIX 

Ricardo

[Anonymous]

Thanks for the very fast respose.

But something is wrong - see this example, where your file results in 20.000 for each department?

Maybe it needs use of your two columns named Key?

This post wouldnt upload my picture so her is the example in plain text:

DIMSplit
aaa	90-1	150
aaa	90-2	150
aaa	90-3	200
bbb	90-1	50
bbb	90-2	50

FACTTrans
01-01-2020	10.000	90-1	aaa
01-01-2020	20.000	90-1	aaa
01-01-2020	30.000	90-1	bbb

Expected result
90-1	24.000
90-2	24.000
90-3	12.000
	60.000

[camargos88]

@Anonymous ,

I didn't get how you connected your tables. So I created a bridge with ID column and connected both tables on it.

The total for row is calculated by:

Share = sum of Share by Dep

Value = sum if Share by ID

Amount = sum of amount by ID

After that I just applied the formula (Share / Value) * Amount.

Once the tables are connected by ID, the value will the same for the row total.

If this is not correct, in order to help you I need to understand how you are connecting it.

Ricardo

[Anonymous]

@camargos88

Relations between the tabels is part of the question 🐵 

And I will follow your advice, very much appriciated.

I've tried with two bridge tables, ID an your 'Key', combined with USERELATIONSHIP, but with no luck.

Here you have a better example, where

1. dep 90-1 has transactions with two differenct Split ID in january
2. 90-1 has lines without Split ID (In many lines in the real FACTTrans, SplitID is null, and the full amount is allocated to the department in column 'Dep')

[Anonymous · Accepted solution]

I will give a step by step solution to this. I suggest you start with a blank Power BI file and start from scratch for better understanding.

Step 1: Add the DIMSplit table to your data model using the "New table" option using the following code.

DIMSplit =
DATATABLE (
    "ID", STRING,
    "Dep", STRING,
    "Share", INTEGER,
    {
        { "aaa", "90-1", "150" },
        { "aaa", "90-2", "150" },
        { "aaa", "90-3", "200" },
        { "bbb", "90-1", "50" },
        { "bbb", "90-2", "50" }
    }
)

Step 2: Add the "FACTTrans" table to your data model using the "New Table" option using the following code.

FACTTrans =
DATATABLE (
    "Date", DATETIME,
    "Amount", DOUBLE,
    "Dep", STRING,
    "SplitID", STRING,
    {
        { "01-01-2020", "10.000", "90-1", "aaa" },
        { "01-01-2020", "15.000", "90-1", "bbb" },
        { "01-01-2020", "25.000", "90-1", BLANK () },
        { "02-02-2020", "20.000", "90-1", "aaa" },
        { "03-03-2020", "30.000", "90-1", "bbb" }
    }
)

Step 3:  Add the "Expected Result" table to your data model using the "New Table" option using the following DAX code.

ExpectedResult =
VAR SumTable =
    SUMMARIZECOLUMNS ( FACTTrans[Date], DIMSplit[Dep], DIMSplit[ID] )
VAR EmptyIDTable =
    GENERATE (
        SUMMARIZECOLUMNS ( 'FACTTrans'[Date], DIMSplit[Dep] ),
        ROW ( "ID", BLANK () )
    )
RETURN
    UNION ( SumTable, EmptyIDTable )

Step 4: Add the following calculated columns to the "Expected Result" table using the following DAX codes.

Amt =
VAR CurrentDate = ExpectedResult[Date]
VAR CurrentDepartment = ExpectedResult[Dep]
VAR CurrentID = ExpectedResult[ID]
RETURN
    SUMX (
        FILTER (
            FACTTrans,
            FACTTrans[Dep] = CurrentDepartment
                && FACTTrans[Date] = CurrentDate
                && FACTTrans[SplitID] = CurrentID
        ),
        FACTTrans[Amount]
    )

DateIDAmount =
VAR CurrentDate = ExpectedResult[Date]
VAR CurrentID = ExpectedResult[ID]
VAR Sum1 =
    SUMX (
        FILTER (
            ExpectedResult,
            ExpectedResult[Date] = CurrentDate
                && ExpectedResult[ID] = CurrentID
        ),
        ExpectedResult[Amt]
    )
VAR Sum2 = ExpectedResult[Amt]
RETURN
    IF ( ISBLANK ( ExpectedResult[ID] ), Sum2, Sum1 )

PercentageSplit =
VAR CurrentID = ExpectedResult[ID]
VAR CurrentDep = ExpectedResult[Dep]
VAR DepIDAmount =
    SUMX (
        FILTER ( DIMSplit, DIMSplit[Dep] = CurrentDep && DIMSplit[ID] = CurrentID ),
        DIMSplit[Share]
    )
VAR IDAmount =
    SUMX ( FILTER ( DIMSplit, DIMSplit[ID] = CurrentID ), DIMSplit[Share] )
RETURN
    IF ( ISBLANK ( ExpectedResult[ID] ), 1, DIVIDE ( DepIDAmount, IDAmount, 0 ) )

FinalAmount =
ExpectedResult[DateIDAmount] * ExpectedResult[PercentageSplit]

Step 5: Add a matrix visual to your report and use the Expected Result table's date(month) and Department fields to column and rows respectively and add the "Final Amount" field to the "Values" section of the matrix which will give you the following result.

This is not an optimum solution, but nevertheless you will get the results and you can also see the intermediate results at every step. Later on, you may simplify the codes or rewrite the same using measures though that will be consuming your CPU power a lot if your dataset is quite large. 

Please let me know if you have any trouble understanding any of the DAX code snippets. By the way, there are no relationships between any of these tables.

[Anonymous]

@Anonymous

Awesome.

I will study this carefully.

When you write This is not an optimum solution, do you think performace?

Its important for me to think of performance, because of large datasets, and can there be an issue with

• the use of temporary tables
• the use of SUMX(FILTER ...)
• no use of relations

?

regards, Henrik

[Anonymous]

Regarding my disclaimer about the optimization of code, we can achieve the results with less and more efficient code if our data model is correct.

1) For example, whenever there is a date/time related analysis, almost always does, I maintain a "Calendar" table in my data models and mark it as a date table. Before that, I disable the "Auto Date/Time" in the File->Options And Settings->Options-> Data Load menu of the file. There is a reason to do so, which is quite elaborate to explain here.

2) There is a many-to-many relationship in your file - The SplitID in Fact & Dim tables.  Usually, to handle this, we need a bridge table.

3) For better performance, if there is no multi-column predicate in FILTER which returns a table, we may use CALCULATETABLE function which is better in terms of performance. But whenever there is a CALCULATE / CALCULATETABLE, there are more things to consider like context transitions, code/measure reusability, etc...

4) If we have a proper data model with the right relationships between tables, the amount of DAX code necessary for achieving results is considerably less.

5) Without complete knowledge of the data model and volume of records, generally, the first step is to solve the challenge in a step by step way and not worry about optimization. Once we have the right results and with a proper understanding of the complete data model, we can optimize and simplify further - this part only you can do.

[Anonymous]

@Anonymous

Thanks for the great explanation.

I do have a date calendar in my model, and FACTTrans I think will be less than 1 mio. 

I can make a brigde table if needed.

If you can guide me to the needed bridge table and guide me in the direction of the optimal code, that would be great.

I will study your step by step anyway, for sure 🙂

Regards

Henrik

[Anonymous · Accepted solution]

Hi @Anonymous 

Using a small bridge table, you can achieve the results using a measure itself instead of using the "Expected Result" calculated table and the other "calculated columns" in that table.

The 2nd solution using measure is as follows...

Prerequisite: Open a blank Power BI file and complete Step 1 & Step 2 mentioned earlier to create the "DIMSplit" and "FACTTrans" tables.

Step 3: Create a bridge table - Use calculated table / New table with the following DAX code.

IDs_BridgeTable = ALL(DIMSplit[ID])

Step 4: Create the following relationships.

IDs_BridgeTable[ID] -> DIMSplit[ID], One to Many, Active relationship

IDs_BridgeTable[ID] -> FACTTrans[SplitID], One to Many, Active relationship

DIMSplit[Dep] <-> FACTTrans[Dep], Many to Many, Inactive relationship

 Step 5: Create a Measure

ExpectedResult =
VAR IT_TABLE =
    ADDCOLUMNS (
        SUMMARIZE ( DIMSplit, DIMSplit[ID], DIMSplit[Dep] ),
        "ID_Dep_Amount", CALCULATE (
            SUMX ( FACTTrans, FACTTrans[Amount] ),
            CROSSFILTER ( DIMSplit[ID], IDs_BridgeTable[ID], BOTH )
        ),
        "ID_Total", CALCULATE ( SUMX ( DIMSplit, DIMSplit[Share] ), ALL ( DIMSplit[Dep] ) ),
        "ID_Dep_Share", CALCULATE ( SUMX ( DIMSplit, DIMSplit[Share] ) )
    )
VAR Res1 =
    SUMX ( IT_TABLE, [ID_Dep_Amount] * DIVIDE ( [ID_Dep_Share], [ID_Total], 0 ) )
VAR Res2 =
    CALCULATE (
        SUMX ( FILTER ( FACTTrans, FACTTrans[SplitID] = "" ), FACTTrans[Amount] ),
        USERELATIONSHIP ( DIMSplit[Dep], FACTTrans[Dep] )
    )
VAR Result = Res1 + Res2
RETURN
    Result

Step 6: Use a matrix visual and use DIMSplit[Dep] on Rows, FACTTrans[Date].Month on Columns, and the measure "Expected Result" created on Step 5 in the "Values" section of the matrix to get the following result.

The major difference between the earlier solution and this is that the earlier one is relying on calculated tables and calculated columns created using DAX and the result is stored in the data model in a separate table. But in this case, the result is calculated using only a measure without the need for any calculated table or calculated columns except for the bridge table in many to many relationship. We could have avoided the bridge table also, but it will make the measure more complicated.

You may try both approaches in your data model and use "Performance Analyser" to evaluate and compare the performance between both the approaches and its difference will be evident if you use a really large dataset.  It is up to you to decide between the two approaches.

[Anonymous]

Just awesome, thanks a lot 🐵