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

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]
)
  • drwillia's avatar
    drwillia
    Helper I
    4 years ago

    Hi tamerj1 im having issue downloading the file. Let me digest and come back to you, thank you.

  • drwillia's avatar
    drwillia
    Helper I
    4 years ago

    Hi tamerj1 

     

    Thanks for posting, the issue i have now is if i add components to the Allocation table it generates a combination for every component

     

    CommodityComponentAllocation
    (5) DTTransmission30.00%
    (5) DTTorque Converter10.00%
    (5) DTFinal Drive50.00%
    (5) DTDifferential10.00%
    (6) HYDMajor Cylinders100.00%
    (9) STROther STR100.00%
    (2) ENGEngines100.00%
    (8) E&EOther E&E100.00%
    (6) H&COther H&C100.00%
    (7) F&FOther F&F100.00%
    (1) UCOther UC100.00%
    EMP Other EMP100.00%

     

    So for (5) DT i should only generate for Transmission, Torque Converters, Final Drive & Differential but in reality it is generating for all combinations. 

     

    Am i making sense?

    Thanks

    Daniel

    • tamerj1's avatar
      tamerj1
      Community Champion
      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]
)
      • drwillia's avatar
        drwillia
        Helper I
        4 years ago

        You are a master, thank you. ğŸ˜€