cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: Calculate Rows of a Measure

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Regular Visitor

Re: Calculate Rows of a Measure

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
Highlighted
Super User V
Super User V

Re: Calculate Rows of a Measure

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/
Highlighted
Super User IX
Super User IX

Re: Calculate Rows of a Measure

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Regular Visitor

Re: Calculate Rows of a Measure

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

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors