How to address a calculation where I have a deal size and a value in each row to do some calculation

[zzzsharepoint]

So I have a table A where I have Region and Cost for each month like below. Then I have a different table B where I have Companies, Region and DealSize for each month as below table:

I would like to calculate:

1) allocated ratio for each company by formulae (Deal size for that month/Sum(DealSize for all the companies in that region in that month))

2) Allocated cost for each company : allocated ratio of that company(from step 1 above) * Cost of the region for that month

Tables I have:

Table A

Region	Cost for April	Cost for May	Cost for June
Europe	40000	90000	210000
America	10000	910000	110000
Asia	230000	410000	410000
Africa	110000	140000	240000

Table B:

Region	Companies	Deal Size for April	Deal Size for May	Deal Size for June
Europe	Company AEur	3	43	33
Europe	Company BEur	32	2	1
Europe	Company CEur	22	13	11
Africa	Company AfA	11	10	21
Africa	Company BfA	21	43	10

What I need is something like this :(Its only for 1 month for companies in Europe and Africa..i need for rest month and for rest region as well)

Companies	Allocated ratio  for April	Allocated price for April
Company AEur	5%	2000
Company BEur	56%	22400
Company CEur	38%	15438
Company AfA	34%	37400
Company BfA	65%	72187

Would like to know how can I best approach the solution with these two tables as my input tables

[v-rzhou-msft · Accepted solution]

Hi @zzzsharepoint ,

Here I suggest you to use unpivot function and some other functions to transform your table.

Table A:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci0tyi9IVdJRMjEAAiBtCaWNDMGMWJ1oJcfc1KLM5ESgoCFMEYxhiKSqOBOkxMgYKmViiMoAq0mDGQQ3AGavEYQRGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Region = _t, #"Cost for April" = _t, #"Cost for May" = _t, #"Cost for June" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cost for April", Int64.Type}, {"Cost for May", Int64.Type}, {"Cost for June", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Region"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns","Cost for ","",Replacer.ReplaceText,{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Attribute", "Month"}, {"Value", "Cost"}}),
    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "MonthNo", each if [Month] = "April" then 4 else if [Month] = "May" then 5 else if [Month] = "June" then 6 else null)
in
    #"Added Conditional Column"

Table B:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci0tyi9IVdJRcs7PLUjMq1RwBIoAucZAbAIijI2VYnWwqHOCqjMCEiBsiF2ZM0SZEVgJyDxDiELHtKLM5ERke9McIbJAwgCkA4c6J7A6I0OY+wwNFIBAKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Region = _t, Companies = _t, #"Deal Size for April" = _t, #"Deal Size for May" = _t, #"Deal Size for June" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Deal Size for April", Int64.Type}, {"Deal Size for May", Int64.Type}, {"Deal Size for June", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Region", "Companies"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns","Deal Size for ","",Replacer.ReplaceText,{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Value", "Deal Size"}, {"Attribute", "Month"}}),
    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "MonthNo", each if [Month] = "April" then 4 else if [Month] = "May" then 5 else if [Month] = "June" then 6 else null)
in
    #"Added Conditional Column"

Measure:

Allocated ratio = 
VAR _Dealsize_thismonth =
    CALCULATE ( SUM ( 'Table B'[Deal Size] ) )
VAR _Total =
    CALCULATE (
        SUM ( 'Table B'[Deal Size] ),
        FILTER (
            ALL ( 'Table B' ),
            'Table B'[Month] = MAX ( 'Table B'[Month] )
                && 'Table B'[Region] = MAX ( 'Table B'[Region] )
        )
    )
RETURN
    DIVIDE ( _Dealsize_thismonth, _Total )

Allocated price = 
VAR _Cost =
    CALCULATE (
        SUM ( 'Table A'[Cost] ),
        FILTER (
            ALL ( 'Table A' ),
            'Table A'[Month] = MAX ( 'Table B'[Month] )
                && 'Table A'[Region] = MAX ( 'Table B'[Region] )
        )
    )
RETURN
    _Cost * [Allocated ratio]

Create a matrix and result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.