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