Forum Discussion
Calculate Rows of a Measure
I work for a consumer product goods company and could really use some wisdom to help me get past an issue I am having.
I created a Measures table and in it I built a number of calculations to help me analyze sales data by region, state, product, etc,; and see this data by Month-to-Date, Year-to-Date, and Rolling 12. I have also built measures to view these same measures but for the previous year. I have then created another measure that compares this data and gives me the variance (+/-) and % change.
I now want to analyze our sales areas and see, over a period of years, how well we have done. For this, I am using my Year-to-Date Variaence (YTD Var) measure which looks at the current year total and the previous year total and shows me how much we were up or down in comparison to the previous year.
Where I am stuck is now I would like to calculate each year that is being displayed in my matrix table and give me a total that takes into consideration the YTD Var from each year. I have some dummy data in my visualization below. When I add a Total Column it is not doing what I would like. For example, Missouri is showing a total of 70, which comes from the 2019 data but it should be (2015 + 2016 + 2017 + 2018 + 2019). In the case of Missouri I would like to see a total of 400; DC should be 96.
I can't seem to figure out a dynamic measure that would add whatever rows are in my filer and give me the total for the row. In some instances I may look at 5 years worth of data, others it may be 3. I would like that total to change based on the dates I have filtered.
My measure I am using are:
- YTDTY = TOTALYTD(sum(Depletion[Total 9L]),Dates[Date])
YTDLY = TOTALYTD(Sum(Depletion[Total 9L]),SAMEPERIODLASTYEAR(Dates[Date]))
YTD Var = [YTDTY]-[YTDLY]
My row comes from the account table that contains the customer state information and my column comes from a Date table I setup.
Dalla_Terra , This because of row context and recalculation of measure grand total. The context of Grand total is also year and input is last year, that is why you see only last year
So one way out is that ytd/this year you use
sum(Depletion[Total 9L])
or try this
new YTDTY = Sumx(Values(Dates[Year]),[YTDTY])
Thanks to you both, your feedback put me on the right path to come up with the modifications I needed to make to make my calc work.
Previously my measures were:
- YTDTY = TOTALYTD(sum(Depletion[Total 9L]),Dates[Date])
YTDLY = TOTALYTD(Sum(Depletion[Total 9L]),SAMEPERIODLASTYEAR(Dates[Date]))
YTD Var = [YTDTY]-[YTDLY]
I modified my YTDTY and YTDLY as follows:
- YTDTY = Sumx(Values(Dates[Year]),TOTALYTD(Sum(Depletion[Total 9L]),Dates[Date]))
- YTDLY = Sumx(Values(Dates[Year]),TOTALYTD(Sum(Depletion[Total 9L]),SAMEPERIODLASTYEAR(Dates[Date])))
Leaving my YTD Var the same gave me what I was expecting to see
3 Replies
Dalla_Terra , This because of row context and recalculation of measure grand total. The context of Grand total is also year and input is last year, that is why you see only last year
So one way out is that ytd/this year you use
sum(Depletion[Total 9L])
or try this
new YTDTY = Sumx(Values(Dates[Year]),[YTDTY])
Hi,
To your visual, you should simply be dragging this measure
Total = sum(Depletion[Total 9L])
Thanks to you both, your feedback put me on the right path to come up with the modifications I needed to make to make my calc work.
Previously my measures were:
- YTDTY = TOTALYTD(sum(Depletion[Total 9L]),Dates[Date])
YTDLY = TOTALYTD(Sum(Depletion[Total 9L]),SAMEPERIODLASTYEAR(Dates[Date]))
YTD Var = [YTDTY]-[YTDLY]
I modified my YTDTY and YTDLY as follows:
- YTDTY = Sumx(Values(Dates[Year]),TOTALYTD(Sum(Depletion[Total 9L]),Dates[Date]))
- YTDLY = Sumx(Values(Dates[Year]),TOTALYTD(Sum(Depletion[Total 9L]),SAMEPERIODLASTYEAR(Dates[Date])))
Leaving my YTD Var the same gave me what I was expecting to see
