Calculate team benefit per month for each product type

[marzeind]

Hi, first of all, sorry for my english.

I need to calculate total benefit for each team per month with different product types. My data look like this tabel:

ID

Product Type

Production Date

Shipping Date

Manufacturer

YearMonth 1

Benefit YearMonth 1

YearMonth 2

Benefit YearMonth 2

YearMonth 3

Benefit YearMonth 3

Split Benefit to Manufacture A

Split Benefit to Manufacture B

Split Benefit to Manufacture C

1

XXX

1/1/2020

1/2/2020

A

Feb-20

2440

Mar-20

4135

Apr-20

2750

100%

2

YYY

1/15/2020

1/16/2020

B

Feb-20

3900

Mar-20

1454

60%

40%

3

ZZZ

1/29/2020

1/30/2020

C

Apr-20

4663

May-20

2826

Jun-20

2872

30%

50%

20%

4

YYY

2/12/2020

2/13/2020

C

May-20

3460

Jun-20

2305

Jul-20

3518

100

5

ZZZ

2/26/2020

2/27/2020

B

Aug-20

4137

100

I need to summary those data for each team, month, and product type, with table like this (table below). Total benefit should be calculate based on manufacture share/split (see table above, 3 last column), different production ID has different split percentage for each manufacturer. 

I still can't find formula in dax to solve my problem😢. If anyone has experiences or has ideas to solve my problem, I really appreciated.

Manufacturer	Month	Product Type	Total Benefit
A	Jan	XXX
A	Feb	XXX
A	Mar	XXX
A	Apr	XXX
A	May	XXX
A	Jun	XXX
A	Jul	XXX
A	Aug	XXX
A	Jan	YYY
A	Feb	YYY
A	Mar	YYY
A	Apr	YYY
A	May	YYY
A	Jun	YYY
A	Jul	YYY
A	Aug	YYY
A	Jan	ZZZ
A	Feb	ZZZ
A	Mar	ZZZ
A	Apr	ZZZ
A	May	ZZZ
A	Jun	ZZZ
A	Jul	ZZZ
A	Aug	ZZZ
B	Jan	XXX
B	Feb	XXX
B	Mar	XXX
B	Apr	XXX
B	May	XXX
B	Jun	XXX
B	Jul	XXX
B	Aug	XXX
B	Jan	YYY
B	Feb	YYY
B	Mar	YYY
B	Apr	YYY
B	May	YYY
B	Jun	YYY
B	Jul	YYY
B	Aug	YYY
B	Jan	ZZZ
B	Feb	ZZZ
B	Mar	ZZZ
B	Apr	ZZZ
B	May	ZZZ
B	Jun	ZZZ
B	Jul	ZZZ
B	Aug	ZZZ
C	Jan	XXX
C	Feb	XXX
C	Mar	XXX
C	Apr	XXX
C	May	XXX
C	Jun	XXX
C	Jul	XXX
C	Aug	XXX
C	Jan	YYY
C	Feb	YYY
C	Mar	YYY
C	Apr	YYY
C	May	YYY
C	Jun	YYY
C	Jul	YYY
C	Aug	YYY
C	Jan	ZZZ
C	Feb	ZZZ
C	Mar	ZZZ
C	Apr	ZZZ
C	May	ZZZ
C	Jun	ZZZ
C	Jul	ZZZ
C	Aug	ZZZ

Thanks in advance....🙏

[amitchandak · Accepted solution]

@marzeind , Copy this code in blank query in power query(transform data) and check

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVFNC8IwDP0rY+BtsjZJu+04BQ+C932wg4J4ERFhB/+9abZ2HXgYLM17LH0vSfs+1WmWNk3Dp851DgqUQPCw5jjdb3shQOTS5fqZOGk0ruY9cyiMXFdqxymRGLI+BUZt204mZnHR1uNDqI/ssFIrO02GONmgToycPjLuum7qvFr0UXl8jNska1F0v3PbJVhO5/HleeE6RvExcsLsRGESyHVYEmOMnYIyklUrZVRG+HP+b3QZTc6LExcT5mELu7hA8W9fPurxEZ6liBT9KzDY9LnaZHPx8AM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Product Type" = _t, #"Production Date" = _t, #"Shipping Date" = _t, Manufacturer = _t, #"YearMonth 1" = _t, #"Benefit YearMonth 1" = _t, #"YearMonth 2" = _t, #"Benefit YearMonth 2" = _t, #"YearMonth 3" = _t, #"Benefit YearMonth 3" = _t, #"Split Benefit to Manufacture A" = _t, #"Split Benefit to Manufacture B" = _t, #"Split Benefit to Manufacture C" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Product Type", type text}, {"Production Date", type date}, {"Shipping Date", type date}, {"Manufacturer", type text}, {"YearMonth 1", type date}, {"Benefit YearMonth 1", Int64.Type}, {"YearMonth 2", type date}, {"Benefit YearMonth 2", Int64.Type}, {"YearMonth 3", type date}, {"Benefit YearMonth 3", Int64.Type}, {"Split Benefit to Manufacture A", Percentage.Type}, {"Split Benefit to Manufacture B", type number}, {"Split Benefit to Manufacture C", type number}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "Product Type", "Production Date", "Shipping Date", "Manufacturer", "Split Benefit to Manufacture A", "Split Benefit to Manufacture B", "Split Benefit to Manufacture C"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1","YearMonth ","",Replacer.ReplaceText,{"Attribute.2"}),
#"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute.1]), "Attribute.1", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute.2", "Month No"}}),
#"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Renamed Columns", {"ID", "Product Type", "Production Date", "Shipping Date", "Manufacturer", "Month No", "YearMonth", "Benefit"}, "Attribute", "Value"),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Unpivoted Columns1", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Attribute.1", "Attribute.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"Attribute.2", "New Manufacturer"}, {"Value", "Benefit %"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Actual Benefit", each [Benefit]*[#"Benefit %"])
in
#"Added Custom"

[marzeind]

thanks for your help @amitchandak, this solution really fix my problems. thanks a lot...