Dynamic Allocation % Table

[Anonymous]

Hi All, 

I'm trying to create a % allocation table based on actual sales data available in another table. 

The allocation % should be calculated on different periods of time based on YTD revenues. I would like to create a different table in order to store past allocation bases at each point of time

Here below in excel what i need to recreate in Power Query

BU Name	Date	Item	Sales €
BU-1	31/01/2020	Item-2	10
BU-1	29/02/2020	Item-14	91
BU-1	29/02/2020	Item-12	65
BU-1	31/03/2020	Item-14	81
BU-2	31/03/2020	Item-4	66
BU-2	31/01/2020	Item-18	39
BU-2	31/01/2020	Item-10	40
BU-2	29/02/2020	Item-20	67
BU-2	29/02/2020	Item-1	64
BU-2	29/02/2020	Item-18	48
BU-2	31/03/2020	Item-8	84
BU-2	31/03/2020	Item-4	45
BU-2	31/03/2020	Item-20	56
BU-3	29/02/2020	Item-20	50
BU-3	31/01/2020	Item-1	24
BU-3	29/02/2020	Item-18	83
BU-3	31/01/2020	Item-7	58
BU-3	31/01/2020	Item-6	35
BU-3	31/03/2020	Item-11	99
BU-3	31/03/2020	Item-11	71

From a single data table / source  like the one I would like to automatically  

1) Calculate the YTD revenue at different (defined) points in time

		Revenue YTD
	BU-1	BU-2	BU-3	Total
January YTD	10	79	117	206
February YTD	166	258	250	674
March YTD	247	509	420	1176

2) Calculate the YTD allocation % based on the YTD revenues at point in time 

Step  2 is the ultimate goal

		Allocation Base
	BU-1	BU-2	BU-3	Total
January YTD	5%	38%	57%	100%
February YTD	25%	38%	37%	100%
March YTD	21%	43%	36%	100%

Do you have any suggestions to solve my problem?

Thank you a lot!

[Anonymous · Accepted solution]

Unfortunately it still get stuck 😞

I have solved it by replicating the souce sales dataset by each report period (e.g. 31/12/19, 31/01/2020). Marking it with a specific column (report date) and then grouping by this new column and by BU to find out sales by bu YTD

Thanks

Maturin

[edhans]

Hi @Anonymous ,

As much as I love Power Query, and I do love Power Query, I do not think it is the right place for this. DAX is much better at this type of analysls. However, I returned this:

Here is the code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZIxDsMgDEXvwpwI2xgDa7ceoFOUsWO33l/FDIiQBLp9rMe3v+VtM4/XimYxDi2gJSDIj+f3/VkpCwSzL5WhZIEODHJWCSeQOolvIe3mzk6xOtElpIxIzxzHxqjlNIFUMbTQee6iJIwhTSM8YXQkjuNsykSe52c/ZorydUnuPpqHFrpYkn7lsVGJFt3YKGizOGZEq75nuhvRiVL6Awr5kPYf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"BU Name" = _t, Date = _t, Item = _t, #"Sales €" = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-BM"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Sales €", Int64.Type}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type",
            {"BU Name"}, 
            {
                {
                    "All Rows",
                    each
                        let
                            varTable = _
                        in
                        Table.AddColumn(
                            _,
                            "YTD Revenue",
                            each
                                let 
                                    varCurrentMonthEnd = Date.EndOfMonth([Date]),
                                    varCurrentYear = Date.Year([Date])
                                in
                                List.Sum(
                                    Table.SelectRows(
                                        varTable,
                                        each Date.Year([Date]) = varCurrentYear and [Date] <= varCurrentMonthEnd
                                    )[#"Sales €"]
                                )
                        ), 
                    type table [BU Name=nullable text, Date=nullable date, Item=nullable text, #"Sales €"=nullable number, YTD Revenue = nullable number]
                    }
                }
            ),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Date", "Item", "YTD Revenue"}, {"Date", "Item", "YTD Revenue"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded All Rows",{"BU Name", "Date", "YTD Revenue"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    MonthlyTotal = Table.Group(#"Removed Duplicates", {"Date"}, {{"YTD Total", each List.Sum([YTD Revenue]), type nullable number}}),
    PercentOfTotal = 
        Table.AddColumn(
            #"Removed Duplicates",
            "Percent of Total",
            each
                let
                    varCurrentMonth = [Date]
                in
            [YTD Revenue] /
            List.Sum(
                Table.SelectRows(
                    MonthlyTotal,
                    each [Date] = varCurrentMonth
                    )[YTD Total]
                ),
                Percentage.Type
            ),
    #"Removed Other Columns1" = Table.SelectColumns(PercentOfTotal,{"BU Name", "Date", "Percent of Total"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns1", List.Distinct(#"Removed Other Columns1"[#"BU Name"]), "BU Name", "Percent of Total", List.Sum)
in
    #"Pivoted Column"

I could probably spend some time optimizing it and reducing the steps, but it gets tedious dealing too much with nested lists, tables, and records. 

If you are going to load this into the DAX Data model, I strongly suggest you get rid of the last pivot step and load it so it looks like this:

Then you can more easily filter the table or columns in DAX va having BU 1/2/3 in separate columns.

I make no claims on the performance on large data sets. I think I need to go back and do another Table.Group to ensure optimized performance, but after half an hour, my brain is a bit foggy on working with more embedded tables. 😂

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[Anonymous]

Hi @edhans ,

thank you so much for you help and suggestion

Thruth to be told, I was trying to avoid DAX as the result of this problema need to be used in several other Power Query steps. 

I've tried to get the code running on my dataset but unfortunately it gets stuck in the calculatio of the "PercentOfTotal" step. It might be due to my larger dataset (more than 5.000 rows).

Would your code adapt and manage the change of year (I have one single dataset with data from several years)

Thanks again for your help, 

Maturin

[edhans]

Yeah, it generates the monthly totals in the MonthlyTotal step, then it has to do a Table.SelectRows (a filter) on that based on the current month. So with 5,000 rows, it is doing 5,000 Table.SelectRows. This is what Power Query is not good at. 

However, try this. I did a grouping by month, then did the Table.SelectRows, then re-expanded. So it should cut down the filtering on a per month basis vs every single row.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZIxDsMgDEXvwpwI2xgDa7ceoFOUsWO33l/FDIiQBLp9rMe3v+VtM4/XimYxDi2gJSDIj+f3/VkpCwSzL5WhZIEODHJWCSeQOolvIe3mzk6xOtElpIxIzxzHxqjlNIFUMbTQee6iJIwhTSM8YXQkjuNsykSe52c/ZorydUnuPpqHFrpYkn7lsVGJFt3YKGizOGZEq75nuhvRiVL6Awr5kPYf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"BU Name" = _t, Date = _t, Item = _t, #"Sales €" = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-BM"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Sales €", Int64.Type}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type",
            {"BU Name"}, 
            {
                {
                    "All Rows",
                    each
                        let
                            varTable = _
                        in
                        Table.AddColumn(
                            _,
                            "YTD Revenue",
                            each
                                let 
                                    varCurrentMonthEnd = Date.EndOfMonth([Date]),
                                    varCurrentYear = Date.Year([Date])
                                in
                                List.Sum(
                                    Table.SelectRows(
                                        varTable,
                                        each Date.Year([Date]) = varCurrentYear and [Date] <= varCurrentMonthEnd
                                    )[#"Sales €"]
                                )
                        ), 
                    type table [BU Name=nullable text, Date=nullable date, Item=nullable text, #"Sales €"=nullable number, YTD Revenue = nullable number]
                    }
                }
            ),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Date", "Item", "YTD Revenue"}, {"Date", "Item", "YTD Revenue"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded All Rows",{"BU Name", "Date", "YTD Revenue"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    MonthlyTotal = 
        Table.Group(
            #"Removed Duplicates", 
            {"Date"},
            {
                {"YTD Total", each List.Sum([YTD Revenue]), type nullable number}
            }
        ),
    #"Grouped Rows1" = 
        Table.Group(
            #"Removed Duplicates", 
            {"Date"}, 
            {
                {
                    "All Rows", 
                    each
                        let
                            varTable = _
                        in
                        Table.AddColumn(
                            _,
                            "Pct of Total",
                            each
                            let
                                varCurrentMonthEnd = Date.EndOfMonth([Date])
                            in
                                [YTD Revenue] /
                                Table.SelectRows(
                                    MonthlyTotal,
                                    each [Date] = varCurrentMonthEnd
                                )[YTD Total]{0}
                            ),
                            type table [BU Name=nullable text, Date=nullable date, YTD Revenue=nullable number, Pct of Total=nullable Percentage.Type]}
            }
        ),
    #"Expanded All Rows1" = Table.ExpandTableColumn(#"Grouped Rows1", "All Rows", {"BU Name", "Pct of Total"}, {"BU Name", "Pct of Total"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded All Rows1", List.Distinct(#"Expanded All Rows1"[#"BU Name"]), "BU Name", "Pct of Total", List.Sum)
in
    #"Pivoted Column"

It returns this.

Kudos are appreciated on any posts that helped.
Let me know if this gets you there. If it does't though, I am not sure Power Query will work for you in this case.

[edhans]

Did this help at all @Anonymous ?

[Anonymous · Accepted solution]

Unfortunately it still get stuck 😞

I have solved it by replicating the souce sales dataset by each report period (e.g. 31/12/19, 31/01/2020). Marking it with a specific column (report date) and then grouping by this new column and by BU to find out sales by bu YTD

Thanks

Maturin

[edhans · Accepted solution]

Hey @Anonymous - glad you found something that worked. I gave it one more try and this groups at a much much higher level. Worth a shot if you are not entirely happy with your existing solution. Returns the same results.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZIxDsMgDEXvwpwI2xgDa7ceoFOUsWO33l/FDIiQBLp9rMe3v+VtM4/XimYxDi2gJSDIj+f3/VkpCwSzL5WhZIEODHJWCSeQOolvIe3mzk6xOtElpIxIzxzHxqjlNIFUMbTQee6iJIwhTSM8YXQkjuNsykSe52c/ZorydUnuPpqHFrpYkn7lsVGJFt3YKGizOGZEq75nuhvRiVL6Awr5kPYf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"BU Name" = _t, Date = _t, Item = _t, #"Sales €" = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-BM"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Sales €", Int64.Type}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type",
            {"BU Name"}, 
            {
                {
                    "All Rows",
                    each
                        let
                            varTable = _
                        in
                        Table.AddColumn(
                            _,
                            "YTD Revenue",
                            each
                                let 
                                    varCurrentMonthEnd = Date.EndOfMonth([Date]),
                                    varCurrentYear = Date.Year([Date])
                                in
                                List.Sum(
                                    Table.SelectRows(
                                        varTable,
                                        each Date.Year([Date]) = varCurrentYear and [Date] <= varCurrentMonthEnd
                                    )[#"Sales €"]
                                )
                        ), 
                    type table [BU Name=nullable text, Date=nullable date, Item=nullable text, #"Sales €"=nullable number, YTD Revenue = nullable number]
                    }
                }
            ),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Date", "Item", "YTD Revenue"}, {"Date", "Item", "YTD Revenue"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded All Rows",{"BU Name", "Date", "YTD Revenue"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    MonthlyTotal = 
        Table.Group(
            #"Removed Duplicates", 
            {"Date"},
            {
                {"YTD Total", each List.Sum([YTD Revenue]), type nullable number}
            }
        ),
    #"Grouped Rows1" = Table.Group(#"Removed Duplicates", {"Date"}, {{"All Rows", each _, type table [BU Name=nullable text, Date=nullable date, YTD Revenue=nullable number]}}),
    AddYTDTotal = 
        Table.AddColumn(
            #"Grouped Rows1",
            "YTD Total",
            each
                let
                    varCurrentDate = [Date]
                in
                Table.SelectRows(
                    MonthlyTotal, 
                    each [Date] = varCurrentDate
                    )[YTD Total]{0}
        ),
    #"Expanded All Rows1" = Table.ExpandTableColumn(AddYTDTotal, "All Rows", {"BU Name", "YTD Revenue"}, {"BU Name", "YTD Revenue"}),
    #"Inserted Division" = Table.AddColumn(#"Expanded All Rows1", "Division", each [YTD Revenue] / [YTD Total], Percentage.Type),
    #"Removed Other Columns1" = Table.SelectColumns(#"Inserted Division",{"Date", "BU Name", "Division"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns1", List.Distinct(#"Removed Other Columns1"[#"BU Name"]), "BU Name", "Division", List.Sum)
in
    #"Pivoted Column"

[v-xuding-msft]

Hi @Anonymous ，

Glad you have resolved it. You could accept your answer as solution. Others who have the same requirement will benefit from this thread. Thanks.