Forum Discussion

Vu_TR's avatar
Vu_TR
Frequent Visitor
3 years ago
Solved

Splitting rows based on condition by ratio.

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.

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    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's avatar
    Vu_TR
    3 years ago

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

8 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    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's avatar
      Vu_TR
      Frequent Visitor
      3 years ago

      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's avatar
        rbriga
        Impactful Individual
        3 years ago

        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.

  • Ahmedx's avatar
    Ahmedx
    Super User
    3 years ago

    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's avatar
      Vu_TR
      Frequent Visitor
      3 years ago

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

  • rbriga's avatar
    rbriga
    Impactful Individual
    3 years ago

    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's avatar
      Vu_TR
      Frequent Visitor
      3 years ago

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    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.