Assistance Needed: Automating Distribution of Projected Responders in Power BI

[krichmond]

I have two main data tables structured as follows:

Response Percent Master: This table includes columns for "Product & Week & Year" (composite key), "Product", "Year", "Week", and "Response Percentage". It holds the response percentages for different products across weeks and years.

Response Projected Master: This table contains columns for "Product & Week & Year" (composite key), "Product", "Year", "Week", and "Projected Responders". It captures the projected number of responders for products over various weeks and years. Currently, it includes data for the current and next year, with plans to update it annually for subsequent years.

The Excel workbook provided serves as a data source resembling Power BI's data model, including a "Target Layout" tab illustrating the desired report format.

In the "Target Layout", considering row 2 for "Product A" during week 7, initial columns (F-K) show zeros, reflecting time periods before week 7. From week 7 onwards, the distribution of "Projected Responders" is spread over 53 weeks (columns L to BL), using a formula that references "Projected Responders" and adjusts based on data from the "Response Percent Master". Once the 53 weeks of formula occurs, everything after that is zeros.

For instance, week 7 (column L) shows a calculation based on the 3,521 projected responders and a response percentage of 0.02105% from "Response Percent Master", resulting in a value of 0.74, which is rounded up to 1. Subsequent columns (M, N) show increased values (72, 637) corresponding to the rising response percentages (2.03816%, 18.08182%) for the respective weeks.

Implementing this dynamic distribution of projected responders across weeks in Power BI proves challenging, especially automating the distribution across columns based on varying start weeks and response percentages from the "Response Percent Master" table.

Seeking guidance on replicating this Excel-based logic in Power BI, particularly automating the week-over-week distribution of "Projected Responders" using response percentages, while accommodating different starting weeks and product percentages.

Response Percent Master TabResponse Projected Master TabTarget Layout Tab

https://www.dropbox.com/scl/fi/0ug1gykgw1i1e7nvvlot8/Sample-Data-For-Power-BI-Post.xlsx?rlkey=epjeab... 

[krichmond]

@v-jingzhan-msft - Just wanted to check in to see if you have any ideas after I provided the additional information above?

[v-jingzhan-msft · Accepted solution]

Hi @krichmond 

Sorry for the late reply. You can try the following code. I expand it to 53*2 weeks range. See Response Projected Master New query in the attachment.  

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlFwVNJRMjIwMgFS5kBsbGpkqBSrg1XeAogNjYwtLHApsARiM0MDnAYYGoAIQ0tLc5wqDEGEibmxqVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Year = _t, Week = _t, #"Projected Responders" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Year", Int64.Type}, {"Week", Int64.Type}, {"Projected Responders", Int64.Type}}),
    
    // You can replicate steps starting from here, or paste the following code to your query and modify step names accordingly 
    
    #"Added Custom" = Table.AddColumn(#"Changed Type", "CY Week", each {1..53*2}),
    #"Expanded CY Week" = Table.ExpandListColumn(#"Added Custom", "CY Week"),
    #"Added Custom1" = Table.AddColumn(#"Expanded CY Week", "MatchingWeek", each let vWeek = [CY Week]-[Week]+1 in if vWeek > 0 and vWeek < 54 then vWeek else -1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"CY Week", Int64.Type}, {"MatchingWeek", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Product", "Year", "MatchingWeek"}, #"Response Percent Master", {"Product", "Year", "Week"}, "Response Percent Master", JoinKind.LeftOuter),
    #"Expanded Response Percent Master" = Table.ExpandTableColumn(#"Merged Queries", "Response Percent Master", {"Response Percentage"}, {"Response Percentage"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Response Percent Master", "Value", each [Projected Responders]*[Response Percentage]),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom2",null,0,Replacer.ReplaceValue,{"Value"}),
    #"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Year", Order.Ascending}, {"Product", Order.Ascending}, {"Week", Order.Ascending}, {"CY Week", Order.Ascending}}),
    #"Added Prefix" = Table.TransformColumns(#"Sorted Rows", {{"CY Week", each if _ < 54 then "CY Week " & Text.From(_, "en-US") else "NY Week " & Text.From(_ - 53 , "en-US"), type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Prefix",{"Product", "Year", "Week", "Projected Responders", "CY Week", "Value"}),
    #"Rounded Up" = Table.TransformColumns(#"Removed Other Columns",{{"Value", Number.RoundUp, Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Rounded Up", List.Distinct(#"Rounded Up"[#"CY Week"]), "CY Week", "Value")
in
    #"Pivoted Column"

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[krichmond]

@v-jingzhan-msft this works as expected in the "Transform Data" screen!!! Thank you! So close!!! When I click "Close & Apply" I get the following error message. Any idea on how to resolve that?

[v-jingzhan-msft]

Hi @krichmond 

According to the error message, there is an incorrect relationship between tables. Check the model view in Power BI Desktop. Is there any relationship related to "Week" column in table "Response Percent Master"? Delete or fix that relationship. 

[v-jingzhan-msft]

Hi @krichmond 

Sorry I'm not able to see the excel due to network restriction. Is it possible to show the desired output and the logic in a screenshot and paste some sample data directly in the reply? Thank you. 

Best Regards,
Jing

[krichmond]

I just updated the post to include some photos. Let me know if you need anything else. Thank you for letting me know.

[v-jingzhan-msft]

Hi @krichmond 

I did this with Power Query. Please download the pbix file to see detailed steps in Power Query Editor. Feel free to ask if you don't understand any step. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[krichmond]

Sorry for the delayed response. I was out of the office for a few days this week. This is really close! I am so appreciative of your help. A couple of questions.

- I see that the values are limited to just five instances. Is this simply due to you using a sample of five weeks to create the template you posted to my question? If so, would this just automatically expand out if I link it up to my data that has all 53 weeks populated with percentages?

- One thing that will start to happen as the current year progresses is the response curve will flow into next year. I only see current fields created. How would what you provided handle when the 53 week response curve flows from current year into next year?

[v-jingzhan-msft]

Hi @krichmond 

For the first question, the answer is Yes. Because I'm not able to access the full sample data, I make up only five weeks data for testing. But it also works when the data is expanded to 53 weeks. You can replicate steps starting from #"Added Custom" step. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlFwVNJRMjIwMgFS5kBsbGpkqBSrg1XeAogNjYwtLHApsARiM0MDnAYYGoAIQ0tLc5wqDEGEibmxqVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Year = _t, Week = _t, #"Projected Responders" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Year", Int64.Type}, {"Week", Int64.Type}, {"Projected Responders", Int64.Type}}),
    
    // You can replicate steps starting from here, or paste the following code to your query and modify step names accordingly 
    
    #"Added Custom" = Table.AddColumn(#"Changed Type", "CY Week", each {1..53}),
    #"Expanded CY Week" = Table.ExpandListColumn(#"Added Custom", "CY Week"),
    #"Added Custom1" = Table.AddColumn(#"Expanded CY Week", "MatchingWeek", each [CY Week]-[Week]+1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"Product", "Year", "MatchingWeek"}, #"Response Percent Master", {"Product", "Year", "Week"}, "Response Percent Master", JoinKind.LeftOuter),
    #"Expanded Response Percent Master" = Table.ExpandTableColumn(#"Merged Queries", "Response Percent Master", {"Response Percentage"}, {"Response Percentage"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Response Percent Master", "Value", each [Projected Responders]*[Response Percentage]),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom2",null,0,Replacer.ReplaceValue,{"Value"}),
    #"Added Prefix" = Table.TransformColumns(#"Replaced Value", {{"CY Week", each "CY Week " & Text.From(_, "en-US"), type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Prefix",{"Product", "Year", "Week", "Projected Responders", "CY Week", "Value"}),
    #"Rounded Up" = Table.TransformColumns(#"Removed Other Columns",{{"Value", Number.RoundUp, Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Rounded Up", List.Distinct(#"Rounded Up"[#"CY Week"]), "CY Week", "Value")
in
    #"Pivoted Column"

For the second question, sorry I don't understand "One thing that will start to happen as the current year progresses is the response curve will flow into next year." well. Can you provide some example data to help describe the desired outcome?

In the current solution, you can find that I add two columns "CY Week" and "MatchingWeek" for help. This may have an affect for the result in next year but I'm not sure now. 

Regards,

Jing

[krichmond]

Your first response worked when I tested it, thank you! For your second response, sorry for the confusion. Let me try to make things a little clearer. We are trying to forecast responses coming in from marketing campaigns using a rolling 53 week window of time. So say a marketing campaign goes out in early January in the current year, most of the 53 week response curve would fall in the current year but a small piece would technically flow into next year. However, as time goes on in the current year, more and more of the response curve would flow into the following year. Then when the close of the current year looms, the majority of the response curve would technically flow into the following year since they happened so late in the year. So using your Response workbook, the Product A, 2024, Week 11 row should run into NY since it is starting at week 11 in the current year. That is why I was trying to show CY weeks 1 - 53 and NY weeks 1-53. So the percentages run in a row, weeks 1 - 53 at which point the marketing campaign is considered mature and won't change. But the response curve results could flow across years like (just dummy data but hopefully helps paint the picture):

CY Week 47	CY Week 48	CY Week 49	CY Week 50	CY Week 51	CY Week 52	CY Week 53	NY Week 1	NY Week 2	NY Week 3
10	9	8	7	6	5	4	3	2	1

I know it is confusing though. Hopefully this explaination helps. If not, let me know.