Forum Discussion
Adjust Calculated Column based on filter applied to another table.
Hi DogManX,
To what I can understand you are making the cumulative sales based on a Sales table that has all the information.
Why instead of making a table that pulls the cumulative values, you don't replace the full table by a measure? If you want Quarter to Date you should do something like this:
Cumulative QTD Sales = TOTALQTD(SUM(Sales_Info[Sales]); Date_Table[Date])
I'm assuming you also have a dates table if not replace the Date_Table part by the Sales Date.
If you then add the dates to your visuals and this measure it will give you QTD amounts, there is also TOTALMTD and TOTALYTD formulas.
Then since measures are calculated in context if you add the slicer for the region the measure will be filter out.
If you wan to have the table for cumulative values that table needs to have also the region of sale in order for you to be abble to filter out.
Regards,
MFelix
MFelix - thank you so much for the response.
I made a table rather than a measure as I wanted to show cumulative sales compared to the previous year. So I have an area graph that shows this year's sales throughout the quarter compared to the previous year. This amount is being used to graphically visualize if we are on pace to meet our various numbers.
Hi DogManX,
In this case you need to also add another measure for the previou year calculation something like:
Cumulative PY = TOTALQTD ( SUM ( Sales_Info[Sales] ); SAMEPERIODLASTYEAR ( Date_Table[Date] ) )
the both measures will be filter by context.
Regards,
MFelix
But will I be able to have a line graph going through the course of the quarter using a measure? I thought I needed a column calculation for it to be more than a single data point?
