Splitting rows based on condition by ratio.

[Vu_TR]

Hello everyone,

I am having an issue with cost allocation in accounting. I have data table 1 as shown below, and allocation ratios in table 2. How can I automatically transform this into data table 3 based on the ratios in table 2?

I hope to receive everyone's help. Thank you very much.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new table.

New Table = 
SUMMARIZE (
    ADDCOLUMNS (
        GENERATE (
            Data,
            FILTER (
                Allocation,
                EOMONTH ( Allocation[Period], 0 ) = EOMONTH ( Data[Date], 0 )
                    && Allocation[Department] = Data[Department]
            )
        ),
        "@AmountXRatio", Data[Amount] * Allocation[Allocation_rate]
    ),
    Data[Date],
    Data[Account],
    Data[Description],
    Allocation[Sub_Department],
    [@AmountXRatio]
)

[Ashish_Mathur]

Hi,

One can write a calculated column formula in Table2 (LOOKUPVALUE() function) to search in 3 columns of Table1 (Date, Department) and bring over the amount from Table1.  Then in Table2, one can simply multiply the 2 columns.

[rbriga]

For the best results, solve it in the query.

If it's a SQL (or SQL-like) source, it would be something like

SELECT
	T1.DATE,
	T1.ACCOUNT,
	T1.DESCRIPTION,
	T2.SUB_DEPARTMENT,
	T1.AMOUNT * T2.ALLOCATION_RATE AS ALLOCATED_AMOUNT
FROM TABLE1 T1
LEFT JOIN TABLE2 T2
	ON DATEADD(MONTH, DATEDIFF(MONTH, 0, T1.DATE), 0) = T2.PERIOD --AND!!!!!!!

But mind that we might be missing another field that tells us to which department each breakdown is relevant.

In a non-SQL source, do the same in the query editor.

[Vu_TR · Accepted solution]

I had never heard of SQL before, but I will learn it to compare with other methods. Thank you very much for your help.

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Splitting rows based on condition by ratio..pbix

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

[Vu_TR]

That's great, it worked really well in Power Query. Thank you for your enthusiastic assistance.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new table.

New Table = 
SUMMARIZE (
    ADDCOLUMNS (
        GENERATE (
            Data,
            FILTER (
                Allocation,
                EOMONTH ( Allocation[Period], 0 ) = EOMONTH ( Data[Date], 0 )
                    && Allocation[Department] = Data[Department]
            )
        ),
        "@AmountXRatio", Data[Amount] * Allocation[Allocation_rate]
    ),
    Data[Date],
    Data[Account],
    Data[Description],
    Allocation[Sub_Department],
    [@AmountXRatio]
)

[Vu_TR]

Although DAX functions are more difficult for me to understand than Power Query, they also perform very well. Thank you @Jihwan_Kim so much for your support.

[rbriga]

Best practice is to push the tranformations into the source- a native query if possible, or a Query Editor transformation. This means real-time calculations are faster, and the measures simpler.