Budget data contains specific Customer records and also an all other record for smaller Customers

Hello Enterprise DNA,
I’ve been struggling with this model setup, and how to utilize it effectively.
I have to compare sales data to our “planned” forecast and budgets, but our forecast/budget data only calls out around 40 specific Customers, and then groups the rest into an “All Other” bucket.

So the Budget key is linked by Part, location, customer specific/all other ID, and sales key is part, location, customer specific ID

Can I relate these and say for all Customer with an “All Other” ID for this date period, take their percent of sales in selected period and apply it to the all other ID forecast record, and use the Customer specific forecast record for the Customers with specific forecasts?

I feel I need to roll sales up to the Customer specific/all other group, and also roll forecast and budgets down to the specific customer level to make this analysis versatile.

Any thoughts?

@capnbigal ,

If I understand the problem correctly, I think you are spot on in your roll up approach. This is a granularity problem, which we typically think about in terms of time granularity in these sorts of scenarios. However, this is a customer granularity issue (which I’ll admit, I’ve never seen in this particular construct before). I think the best approach will be to treat the “all other” as a single composite customer, so in all relevant tables rolling up their individual customer IDs to the single “all other” ID in Power Query.

However, I don’t see the utility in rolling forecasts and budgets down to the specific customer level beyond the 40 already called out. It will make things far easier if your sales and forecasts are both at the same granularity, and doing that at the 40 specific + 1 composite customer level makes sense to me given my understanding of your issues.

If you build your table relationships based on this revised key structure your DAX should fall into place quite nicely, since DAX will have no visibility into the fact that this composite customer key actually contains many separate customers. To DAX, it will just look like a single customer (though one probably with a lot of sales…).

I hope this is helpful.

  • Brian
1 Like

@BrianJ
It is reassuring to know I’m not the only one thinking it is a funky construct!
That makes sense, and thank you for the feedback.

I guess my last question would be the relationships. In the picture I provided, I ended up adding an additional column in the AllCust table for CustSpecID to go along with the CustID, and only had CustID in my sales fact table to establish the relationship. Should I change the relationships to

  1. fact sales → CustID table → CustSpecID table
  2. fact sales ->CustID table and use column in AllCust table for CustSpecID (No CustspecID in fact, only CustID)
  3. fact sales → CustID table and fact sales → CustSpecID table

And does CustID and CustSpecID need to relate to each other? I have 1500 CustIDs, and 40 or so CustSpecIDs

One of the reasons for relating or breaking down the “all other” record to all customers was to see the % of current forecast in place to support. Is this possible through a measure to say if 10 “all other” customers bought 100 a month, even if the “all other” forecast was 0,1, or 29765, it would return 10% for each customer.

This almost sounded like it could be solved with a hierarchy of part, location,custspecid,custid to part,loc,custspecid, to partloc, to part… the same way a product category hierarchy would be. Is that an outlandish approach?

Thanks!

@capnbigal ,

The more I think about this, the more intriguing it gets. I think there’s a way to have our cake and eat it too on this. What if the ID used in Customer is 1 to (N + 1), where N is 1500 or so, and the +1 is the additional row where a separate ID is assigned to the named “composite” customer? Then in the fact table, we have two key fields – CustID and CustSpecID, one connected via active relationship and the other connected via inactive relationship to CustIDN+1. This maintains referential integrity and allow us to dynamically roll or unroll the “Other” category, depending which relationship gets switched on.

Makes the DAX a bit more complicated than the PQ rollup approach I proposed above, but not overly so, and I think gives you a lot of dynamic flexibility in the analysis. Perhaps it might be worth generating a dummy dataset using one of these tools/techniques that we could experiment with?

https://forum.enterprisedna.co/t/tip-tools-and-techniques-for-providing-pbix-files-with-your-forum-questions/17763

Thoughts?

  • Brian
1 Like

Hi @capnbigal, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

I hope that you are having a great experience using the Support Forum so far. Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!

Hi @capnbigal, we’ve noticed that no response has been received from you since July 1st. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

Hi @capnbigal, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.