Forum Discussion
Adjust Calculated Column based on filter applied to another table.
I have a date table (Sales_Totals) where I am calculating the cumulative sales for each day in the quarter pulling the sales numbers from the Sales_Info table based on the Sale Date. So it pulls the cumulative sales for April 1, April 2, etc.
I was wondering if there is a way to setup a slicer so that when I filter for the region of the sale (say East, West, North, South) on the Sales_Info table it will impact the values of the Sales_Total table so that it only shows the cumulative values for that region.
5 Replies
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.
