Forum Discussion

drwillia's avatar
drwillia
Helper I
4 years ago
Solved

Split Row Value by Allocation Table %

Hi, This problem is a little beyond me,  I would like to break each record out based on the Commodity dimension. If that Dimension matches the allocation table then create additonal rows for each ...
Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    drwillia 
    Here is the sample file with the solution https://we.tl/t-Cp8LuiRxh9

    Rebates (Summary) = 
SELECTCOLUMNS (
    GENERATE (
        Rebates,
        ADDCOLUMNS (
            Allocation,
            "Allocated Revenue", Rebates[Revenue] * Allocation[Allocation],
            "Allocated Component", Allocation[Component] 
        )
    ),
    "Date", [Date],
    "Branch Code", [Branch Code],
    "Company Lookup", [Company Lookup],
    "Revenue", [Allocated Revenue],
    "IBP Component", [IBP Component],
    "Source", [Source],
    "Commodity", [Allocated Component]
)
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi drwillia 
    Yes you are absolutely right. Here is the updated file with minor change https://we.tl/t-JjBH8gDGjW

    Rebates (Summary) = 
SELECTCOLUMNS (
    GENERATE (
        Rebates,
        ADDCOLUMNS (
            RELATEDTABLE ( Allocation ),
            "Allocated Revenue", Rebates[Revenue] * Allocation[Allocation],
            "Allocated Component", Allocation[Component] 
        )
    ),
    "Date", [Date],
    "Branch Code", [Branch Code],
    "Company Lookup", [Company Lookup],
    "Revenue", [Allocated Revenue],
    "IBP Component", [IBP Component],
    "Source", [Source],
    "Commodity", [Allocated Component]
)
tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi drwillia 
Do dyou want to create a calculated table? Please provide copy/paste sample data

  • drwillia's avatar
    drwillia
    Helper I
    4 years ago

    Hi tamerj1 . 

     

    This is my current code, which creates a calculated Rebates Table (see Excel attached). 

     

    Rebates (Summary) =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE (
                Rebates,
                Rebates[Month],
                Rebates[Branch Code],
                Rebates[Revised Customer Name],
                Rebates[Commodity],
                Rebates[IBP Component],
                Rebates[Source]
            ),
            "Revenue", CALCULATE ( - SUM ( Rebates[Sum of Dealer Contribution] ) )
        ),
        Rebates[SOS] = "888"
    )
     
    The above code generates the below table
     
    DateBranch CodeCompany LookupRevenueIBP ComponentSourceCommodity
    Thursday, 1 October 202041001 RockhamptonCompany A-384,831.99OtherRebates(5) DT
    Thursday, 1 October 202043003 Mt IsaCompany B-77.51OtherRebates(5) DT
    Thursday, 1 October 202040001 Brisbane OperationsCompany B-504.26OtherRebates(5) DT
    Thursday, 1 October 202040001 Brisbane OperationsCompany A346.39OtherRebates(5) DT
    Thursday, 1 October 202042002 Mackay Hastings ParkCompany B-18,941.76OtherRebates(5) DT
    Thursday, 1 October 202042002 Mackay Hastings ParkCompany A-263,883.13OtherRebates(5) DT
    Thursday, 1 October 202042008 Mackay Connors RoadCompany B7,859.90OtherRebates(5) DT
    Thursday, 1 October 202042008 Mackay Connors RoadCompany A-116,537.24OtherRebates(5) DT
    Thursday, 1 October 202041001 RockhamptonCompany B-3,879.06OtherRebates(5) DT
    Thursday, 1 October 202040002 ToowoombaCompany A-9,617.72OtherRebates(5) DT

     

    The allocation table is below

     

    CommodityComponentAllocation
    (5) DTTransmission30.00%
    (5) DTTorque Converter10.00%
    (5) DTFinal Drive50.00%
    (5) DTDifferential10.00%

     

    So i just need to split out the rows into the 4 allocation components and then multiply each value by the allocation %, does this make sense?

     

    Thanks

    Daniel