Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have 2 tables, 1 dimension table that has a view of all the products and a fact table that has all the products and their targets. We intentionally set higher product targets than our overall goal. In the example below our company target is 200 but the sum of the individual products is actually 225, hence the -25 Prod Adjustment.
Dimension
Product | Country |
Prod A | US |
Prod B | US |
Prod C | US |
Fact
Product | Target |
Prod A | 100 |
Prod B | 50 |
Prod C | 75 |
Prod Adjustment | -25 |
When making a matrix the grand total aggregates properly but not the subtotal. I want the US row to also say 200. There are no other Countries besides US. We just use that for a top level of the hierarchy.
Country | Targets (Actual) |
US | 225 |
Prod A | 100 |
Prod B | 50 |
Prod C | 75 |
Total | 200 |
My current measures are in the attached file if that is helpful. The source of the issue is that [US Target] equals 225 and not 200. I don't know if I am on the right path for best way to accomplish this though. I also use a many to many relationship even though it is not necessary in the example because it is required in our actual workbook.
https://www.dropbox.com/s/kabeww42exurqri/Target%20Adjustment%20Sample.pbix?dl=0
Thanks
I think there is some confusion because that doesn't accomplish what I need. I have to have a many-many relationship because this is only a subset and the actual relationship is many-many. Also, I have to account for the -25 in the US subtotal and not just the grand total and not utilize a blank row.
That is why my measures are as follows:
US Target = CALCULATE(SUM('Product Targets'[Target]), ALL(Products[Product]))
@shep123 , Your relation was many to many. I made it 1 to many and now it shows the blank row.
Please find the file attached after signature
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |