Forum Discussion
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 ...
drwillia
Here is the sample file with the solution https://we.tl/t-Cp8LuiRxh9Rebates (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] )Hi drwillia
Yes you are absolutely right. Here is the updated file with minor change https://we.tl/t-JjBH8gDGjWRebates (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] )
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
| Commodity | Component | Allocation |
| (5) DT | Transmission | 30.00% |
| (5) DT | Torque Converter | 10.00% |
| (5) DT | Final Drive | 50.00% |
| (5) DT | Differential | 10.00% |
| (6) HYD | Major Cylinders | 100.00% |
| (9) STR | Other STR | 100.00% |
| (2) ENG | Engines | 100.00% |
| (8) E&E | Other E&E | 100.00% |
| (6) H&C | Other H&C | 100.00% |
| (7) F&F | Other F&F | 100.00% |
| (1) UC | Other UC | 100.00% |
| EMP | Other EMP | 100.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
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]
)You are a master, thank you. 😀