This topic contains 13 replies, has 3 voices, and was last updated by 
-
Posts
-
Hello Everyone,
I’m new to DAX language and have been experimenting with the same. Though I’m comfortable with DAX upto some extent but not able to apply the DAX context on some of the very common tasks in FP&A (Financial Planning & Analysis). Let me explain it in detail:
The problem is with allocation through measures. I’ve attached a workbook with some dummy data related to Sales Quantity allocation. In the workbook, the task is to allocate budgeted quantity to previous year actual data (“ActBaseDataAllocation” Sheet) and the allocation is to be done on proportionate basis.
I’ve already done the calculations in the attached workbook using simple excel formulas like sumif, index and vlookup. I’ve also successfully attempted the same using PowerPivot but with Calculated Columns using RELATED and CALCULATE. Though, my real problem is that I want to do the same allocation using Measures/Calculated Field.
Would be great if someone can help me on the same. I’ve tried Budget Patterns on sqlbi.com but I was really not able to apply the same logic in my context.
Thanks in advance.
I don’t think this one will be that hard, but I’m still new to this:)
But just so I follow your logic:
For each product cat/sales office/customer combination, you have an actual sales quantity. You calculate what percentage of the total sales that combination has and calculate an equal proportion of the budgeted quantity?
This seems like a job for—–values()!!! I think
Actually, if you observe closely, the “BgtInputSheet” is at the product category level and the main issue is how will a calculated field will handle the same at different granularity levels while plotting the pivot and using the same. I tried attempting the same using the article on Budget Patterns on sqlbi.com but unfortunately I was not successful and hence this post.
Would be great if you can help me on this. Thanks.
Hi Deepak,
This seems more like a data preparation task that should be handled in Power Query before importing into Power Pivot, doing the allocation calculation at the lowest level of granularity.
Once this is complete and the data is imported into Power Pivot, the different granularities would be resolved through pivot filter context and SUM().
Tom
Thanks a lot, Tom. As I’m new to DAX and Power Query, could you please help me with my workbook on how to do the same with power query?
Deepak,
After downloading your workbook, I noticed your Excel formula writing skills are strong and, for now, I recommend using those skills to prepare your data in order to quickly arrive at analysis which should be the reason you are using Power Pivot (a data modeling tool for analysis).
I made a copy of your work on ActBaseDataAllocation and in the new worksheet, I
. Removed the blank column separating the calculated columns
. Copied the values over the formulas
. “Insert”ed the data as an Excel table
. Named the Excel table “Data” (of course, it can be named whatever you want)
. Added the table to the Data Model (Power Pivot)
. Accessed the Power Pivot window to test and format the data
. Created measures
. Returned to Excel and created a pivot.At this point, let me describe just one of the “powerful” benefits of Power Pivot (something Rob refers to as formula portability); something that just is not available in a regular pivot table and, for many Excel pros, has practically doubled their productivity when working with pivots.
The pivot in the attached workbook has Product Category and Product on rows and slicers for Sales Office, Customer Code, and Month. You can re-arrange any of those (rows can go on columns, columns can go on rows, what is on slicers can go on rows or columns, etc.) and the measures in the pivot (“Total Actual Quantity”, “Product Category Proportion” (a percentage), “Total Budget Allocation Quantity”, “Combined Actual + Allocation”) just adapt to the changes–no other re-work. Also the measures named above will work in any Power Pivot pivot table or pivot chart in the same workbook. Formatting also defined in the Power Pivot window is preserved in all pivot tables in the same workbook.
To become more familiar with Power Query, I recommend getting a copy of “M is for (Data) Monkey” by Ken Puls and Miguel Escobar. That book was a game changer for me when it came to using Power Query; I expect it will be the same for you (but work the new skills in gradually, taking advantage of your excellent Excel formula writing skills for now).
*** Workbook to large to upload, the following is a dropbox link for you to download the zip file:
https://www.dropbox.com/s/kxphc2efqindczf/Upload%20ProportionateAllocationTest-1.zip?dl=0
Let me know when you have downloaded workbook, and I will clear dropbox connection.
Tom
Thanks a ton, Tom. I really appreciate the effort you have put in to explain it in such an awesome manner. I’ll be working on enhancing my skills continuously and surely, I’m going to buy the recommended book for Power Query.
You made my day. Thanks again.
And, thanks for the workbook. I’ve downloaded the same.
Dear Tom
I can very well see the number of rows increasing to millions in my business context as I used dummy data only in the sample workbook here. And I see a very big problem with SUMIFS function working on those millions of rows to arrive at those proportions for allocation. With 2 lac rows my system powered by i7 takes close to 7 minutes to calculate the same and you can very well imagine the same with 1 million rows.
In this context, if you can give me some hint on how to execute this proportion business using Power Query, my life would be far more simpler. 😀
Thanks a lot for the support. 🙂
Deepak,
Since your rows could reach millions, is your data source a relational database? (1048576 rows is Excel worksheet limit)
Tom
Sorry for the late reply as it was late here in India. Yes, the source of the data is a relational database. Would be great if you can help me in this regard as Excel gives up on these many rows.
Ideally your relational database could shape your data for you and you could pull it into Excel using Power Query. Most relational databases use SQL as the query language. Is that so for your organization? (even better if SQL Server)
If accessing your data directly from the relational database is not going to work for you, another option to consider is downloading the free version of Power Update that you can learn about by following this link. Power Update can be set up to automatically update a Power Pivot workbook.
As long as we are discussing links and options, here is a link to a preview of Rob’s Comprehensive Power Pivot Course. I have taken the course and recommend it highly.
Tom
Thanks for the inputs, Tom. I’m able to pull all the data in the Power Pivot and able to successfully do the allocation thing using Calculated columns, however, my basic issue is how to do the same either using Power Query or using Calculated Field/Measure because as per all the text that I’ve read so far recommends not to use Calculated Columns when more efficient calculated measures are available.
Moreover, while preparing a budget, it has to go through several iterations meaning the budgeted quantity will keep changing and that’s precisely the reason I want to make the calculations dynamic which will take care of automatic allocation at last year’s proportions.
The forum ‘Power Pivot’ is closed to new topics and replies.