Working with Budgets and Actuals. They are at different levels. Actuals at Product level , Budget at Category level.
The model is designed as a snowflake.
Actuals linked to Product , then linked to Product Category
Budgets linked to Product Category
When I build a Matrix with Budget and Actuals I want
Product Category and ablove to show Budget and Actual value
Below Product Category to show just the Actual and a blank for the budget
to do this I want to change the budget measure to be blank if any member is selected(apart from Product category - which is used for the join but hidden) ,
Which methoid is best ?
ISFiltered
HasOneValue
SlecetedValue
IsInScope
It would be really helpful to see a full snapshot of your data model. Personally, I find snowflakes much harder to work with, so almost always denormalize them to star schema models, which tends to make the DAX much simpler.
@BrianJ
I had started with Star Schema but I end with a many to many when connecting Product to Budget at the category level. This then creates some issues in calculations.
Ended up provisionally with ISFILTERED and used this on the lower level product table. Still testing this out but seems to work.
Just as general food for thought - in dealing with granularity mismatches in my actual vs. budgets analyses, I had previously relied heavily on TREATAS and fairly complex dynamic DAX allocation approaches to address the many-to-many issues. However, the Power Query-based allocation down to the lowest granularity level that @haroonali1000 built Problem of the Week #4 around has been an absolute revelation for me, and has changed the way my team and I do budgeting analyses in Power BI. Not difficult to implement within PQ, and dramatically simplifies the data model and the associated DAX. Your case is a bit different, since the allocation is not time-based, but product/category-based, but I think the general technique could be adapted.
@BrianJ
if only it was as simple as splitting months to days. Unfortunatelly I am working in an environment where there are a lot of quirks \ challenges.
One point I should’ve mentioned earlier is that when you denormalize tables in a snowflake structure, you often have to develop a composite key to avoid the many-to-many problem. In this case, your key would be a composite of product and category, or their corresponding codes. Easy to do In Power Query, either through Column by Examples or a short custom M function using Text.Combine.
@BrianJ
Everything works well now. Not perfect as I would like the report consumers to just see one dimension rather than two for the product.
I was just trying to get my head round the composite key. When in one ‘Product Hierarchy’ table product id would be the key and its the join between Budget or Forecast and the ‘Product hierarchy’ table where the m:n relationship would cause the issue.
So I could mark the budget to the first product in a category or
I could change the product category table to have a column Product Category join id that is only populated with the product category id once per product category.
Delete the two relationships between product category and your budget and forecast tables
Merge product and product category tables on product ID - project category simply becomes an attribute of product
Allocate your budget amount down to a daily granularity
Allocate your forecast amount down to a daily granularity
Merge your budget and forecast tables with your sales table based on date
Here’s the resulting model - star schema, consistent daily granularity, one product dimension table, and you can still roll your forecast amounts or your budget amounts up by project category through the relationship to your product table:
If there’s a downside to this, I’m not seeing it yet. And it will make your DAX a piece of cake…
Brian
EDIT: In thinking more about this, step #5 will probably not be a straightforward merge, but another level of allocation if there are multiple entries for a given date, but still very doable.
@BrianJ
In a simple schema that is a rather excellent idea. You could still choose not to show budget and forecast at Product level.
Very neat solution.
Thanke
E
