I am trying to do something that I hope will be simple, but I just don't underestand enough about DAX yet to figure out how to make it happen. Here is the senario...
We ship product 7 days per week. I have a table that tells every shipment line for each day called Shipments. We measure our shipments by business days, not the actual shipment dates. I have a calendar table called Calendar that references each date to a SaleDate, a FiscalMonth, and a MonthlyDenominator that represents the business days in that month. I produce a bar chart that shows the volume shipped per day that is color coded by FiscalMonth. This all works perfectly.
My challenge is adding a line to the chart that represents the average sales per day for each fiscal month. I created a measure that gives me the answer that I need in a visual grouped by FiscalMonth.
Measure: MonthLine = SUM('Shipments'[Volume]) / AVERAGE('Calendar'[MonthlyDenominator])
When I add this to a visual that is grouped by SaleDate instead of FiscalMonth the line is drawn as the as the daily sum of volume divided by the denominator, rather than the sum of volume from that month divided by the denominator.
To try and fix this problem I created another source table that is just the list of FiscalMonths which I related to the Calendar table. I then added the Measure to that table in hopes that it would by default always be grouped at the month level. But no such luck.
So I am stuck trying to figure out how to reference some data grouped by Date while other data is grouped by Month on the same chart. Help!
Bill
