Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
Hi @DogManX,
The measures are based on context so depending on the information you have on your x-axis you are abble to have several points, If you have quarte only it will give one point, months it will give 3 points days it will give 90 points.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsMFelix - 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsBut 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?
Hi @DogManX,
The measures are based on context so depending on the information you have on your x-axis you are abble to have several points, If you have quarte only it will give one point, months it will give 3 points days it will give 90 points.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |