Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dalla_Terra
Helper I
Helper I

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. 

 

Screen Shot 2020-07-01 at 9.37.19 PM.jpg

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@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])

View solution in original post

Dalla_Terra
Helper I
Helper I

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
Screen Shot 2020-07-02 at 11.38.49 AM.png

View solution in original post

3 REPLIES 3
Dalla_Terra
Helper I
Helper I

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
Screen Shot 2020-07-02 at 11.38.49 AM.png
amitchandak
Super User
Super User

@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])

Ashish_Mathur
Super User
Super User

Hi,

To your visual, you should simply be dragging this measure

Total = sum(Depletion[Total 9L])


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.