1 of 1 people found this helpful
Depending on how you are setting up your view, you might be able to use an aggregate like MIN() or ATTR() on the monthly target when SUM(Daily Sales) gets you that total.
If that doesn't work, then you may need to use a table calculation to accomplish your aggregation, in that case posting a packaged workbook (.twbx) with some sample data would be good.
Thank you for your prompt answer. The MIN() and ATTR() are helpful at the first place, however, it is not working once I try to do some aggregate on those monthly target.
For example, the target # is stored at per product per month, and I could use ATTR() or MIN() to get the correct number displayed at a month/product pivot table. However, when I try to get the total target number for each month, across products, the function didn't give me the number I want.
How to do monthly target number in this case ?
Hey Ken -
Another approach you could take is to simply NOT join all three tables together. Instead:
- Join your date dimension and daily sales fact table together in one data source
- Bring in your monthly goal fact table in as a second, distinct data source
- Use Data Blending to marry the two together.
I've attached a sample, including the 3 tables (in an Excel worksheet) that I created to drive the sample....