Analytics Summit PQ for Budget Allocation

Power Query
1

Dear Forum:

I was fortunate enough to attend yesterdays session where Brian took us through using Power Query to allocate a simple monthly budget. My question is a bout how the steps would differ if you have multiple brands or products to allocate using the same method.

I have attached my file using a slightly different GroupBy approach which appears to work but is showing an error message. Is this the correct way to do the allocation?

I will attach my file so you can see what I am attempting to do, using just two brands.

Any input is sincerely appreciated
PQ Bud Alloc Multi Brands.pbix (468.4 KB)
.

Thank you.
*** READ FIRST ***

Before you send your question.
Make sure that all details relevant to your question is complete:

  • Your current work-in-progress PBIX file - VERY IMPORTANT
  • A clear explanation of the problem you are experiencing
  • A mockup of the results you want to achieve
  • Your underlying data file

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

Also make sure that your data file contains no confidential information. If it does, click the link above.

*** DELETE THIS MESSAGE IF YOU ARE SURE ALL YOUR DETAILS ARE COMPLETE OR IF THE ABOVE INFORMATION IS NOT APPLICABLE TO YOUR QUESTION.***

1 Like
2

@Whitewater100 ,

Glad you found the session helpful. I’m just getting ready for a houseful ofThanksgiving guests to arrive, but will put together a solution on this late tonight or tomorrow morning. Same basic principles apply, just with a couple of twists.

– Brian

3

@Whitewater100 ,

Need to do some PQ work for this solution - can you please post the Excel source files associated with your PBIX?

Thanks!

  • Brian
4

Hi Brian:

Yes I will attach the three files. They are very basic. If it is easier I have the file where you aided a previous question on monthly budget allocation(MyFakeRetailReport). I will attach in case that is a better one to use(more brands with forecast). Either way thank you very much for taking a look at this.

The pbix prior solution is on this page "Qty by Day & Month and your measure was “2020 Forecast Qty Allocation”.

Thanks so much!

Best regards,

Bill
BrandsLatestQuuestion.xlsx (2.2 KB)
MonthlyBudgetLatestQuestion.xlsx (27.2 KB)
SalesDataLatestQuestion.xlsx (14.1 KB)
MyFakeRetailRpt - Solution (1)A.pbix (2.7 MB)

5

@Whitewater100

Hey, Bill. Thanks for the XLSX files. Full solution attached. Here’s the key grouping step and the non-uniform daily allocation M code:

Snag_29256e59
Snag_29256e592098Ă—1578 104 KB

Snag_29259e23
Snag_29259e232098Ă—1314 150 KB

And this generates exactly the allocations expected monthly and daily, uniform and nonuniform:

Snag_29278429
Snag_292784292031Ă—701 40.6 KB

Snag_2927c365
Snag_2927c3652010Ă—739 51.6 KB

I hope this is helpful, and that you find this approach as useful and versatile as I do. Full solution file attached.

  • Brian

eDNA Forum - PQ Bud Alloc Multi Brands Solution.pbix (477.3 KB)

6

Hi Brian:

Outstanding and thank you. This is a great alternative you came up with. I imagine this will prove useful in many instances. Again thank you for your time and expertise!

Best regards,

Bill

1 Like