Forum Discussion
Group Daily Target by Month and compare Weekly Sales
Hi,
This seems like it should be straight forward however I am at a bit of a loss.
I have three tables. A Sales table, a Date table and a Target table.
NOTE: date = 1/1/2017, 1/2/2017, etc...
My sales is split by date/region/store/order ID/Product ID/etc.
My targets are split by date/region/store.
My date table has the date period and every view of date that I need, Month/Year/MonthYear/Week/etc.
I have scoured forums and tried quite a few solutions to no avail and I feel I am missing a basic concept or formula with DAX.
Essentially my table is as below:
| Week | Month | Sales | Target | Achieved% |
| 1 | 1 | 100 | 110 | 90.9% |
| 2 | 1 | 467 | 500 | 93.4% |
| 3 | 1 | 254 | 230 | 110.4% |
| 4 | 1 | 125 | 120 | 104.2% |
| 5 | 2 | 155 | 150 | 103.3% |
| 6 | 2 | 235 | 250 | 94.0% |
| 7 | 2 | 152 | 250 | 60.8% |
| 8 | 2 | 192 | 200 | 96.0% |
| 9 | 3 | 585 | 550 | 106.4% |
| 10 | 3 | 867 | 500 | 173.4% |
| 11 | 3 | 931 | 700 | 133.0% |
| 12 | 3 | 343 | 600 | 57.2% |
And I want it to look like this
| Week | Month | Sales | Target | Contribution % |
| 1 | 1 | 100 | 960 | 10.4% |
| 2 | 1 | 467 | 960 | 48.6% |
| 3 | 1 | 254 | 960 | 26.5% |
| 4 | 1 | 125 | 960 | 13.0% |
| 5 | 2 | 155 | 850 | 18.2% |
| 6 | 2 | 235 | 850 | 27.6% |
| 7 | 2 | 152 | 850 | 17.9% |
| 8 | 2 | 192 | 850 | 22.6% |
| 9 | 3 | 585 | 2350 | 24.9% |
| 10 | 3 | 867 | 2350 | 36.9% |
| 11 | 3 | 931 | 2350 | 39.6% |
| 12 | 3 | 343 | 2350 | 14.6% |
I am not sure if anyone will be able to help me understand a level further in addition to this, however as a Qlik user - I would be able to aggregate any expression and aggregate as required by using AGGREGATE,
e.g. Target_byMonth = AGGREGATE(SUM(Target),[Month Number]) #this gives me a virtual table of summed target by month no..
Then I could simply compare write SUM(Sales) / Target_byMonth and it would return the second table as above.
What is the equivalent in DAX so that I can aggregate my measures by any dimension I want?
Thanks and much appreciated for help!
2 Replies
- anandav
Skilled Sharer
Anonymous,
Try below measures.
Target Cummulative by Month =
CALCULATE(
SUM(Table1[Target]),
FILTER(ALLEXCEPT(Table1, Table1[Month]), Table1[Week] <= Table1[Week])
)New Percentage =
MIN(Table1[Sales]) / [Target Cummulative by Month](Format the above as Percentage)
Hope this helps.
If this solves your problem please mark as solution.
Thanks for replying. It works in the table format using dates from the target table. However I need to use the Calendar table and in a line chart that displays by week (not month), however these are what I'm finding. Could this be an issue with my data model?
I have the Target table linked to my Calendar table (by date which works) and the target table linked to my 'Master Store Table' and both the Calendar and Master Store table are related to my data model? However note my targets work when summing the day without creating the measure you have as below which makes me think it's not the model but the calc? See below for reference.
