Split Row Value by Allocation Table %

[drwillia]

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 Component listed in the allocation table (currently only showing one), once each row has been created multiply the value in revenue by the allocation %

For example the top row is replaced by the four below it,

Rebates (Summary) Table:

Allocation Table:

Please reach out if i have not articulated the problem well enough

Regards

Daniel

[tamerj1 · Accepted solution]

@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 · Accepted solution]

@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]

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

[tamerj1 · Accepted solution]

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]

You are a master, thank you. 😀

[drwillia]

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

[tamerj1]

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

[drwillia]

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

Date	Branch Code	Company Lookup	Revenue	IBP Component	Source	Commodity
Thursday, 1 October 2020	41001 Rockhampton	Company A	-384,831.99	Other	Rebates	(5) DT
Thursday, 1 October 2020	43003 Mt Isa	Company B	-77.51	Other	Rebates	(5) DT
Thursday, 1 October 2020	40001 Brisbane Operations	Company B	-504.26	Other	Rebates	(5) DT
Thursday, 1 October 2020	40001 Brisbane Operations	Company A	346.39	Other	Rebates	(5) DT
Thursday, 1 October 2020	42002 Mackay Hastings Park	Company B	-18,941.76	Other	Rebates	(5) DT
Thursday, 1 October 2020	42002 Mackay Hastings Park	Company A	-263,883.13	Other	Rebates	(5) DT
Thursday, 1 October 2020	42008 Mackay Connors Road	Company B	7,859.90	Other	Rebates	(5) DT
Thursday, 1 October 2020	42008 Mackay Connors Road	Company A	-116,537.24	Other	Rebates	(5) DT
Thursday, 1 October 2020	41001 Rockhampton	Company B	-3,879.06	Other	Rebates	(5) DT
Thursday, 1 October 2020	40002 Toowoomba	Company A	-9,617.72	Other	Rebates	(5) DT

The allocation table is below

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%

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