Forum Discussion

BezosBuddy's avatar
BezosBuddy
Frequent Visitor
9 years ago

Measure value only appearing in Total row

I have a problem with the value of a measure not appearing in the data in a table, and only appearing in the "Total" row.  As shown in the screenshot below, the value appearing in the Total row is co...
v-sihou-msft's avatar
v-sihou-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

BezosBuddy

 

In this scenario, if you directly filter the table, it will take current row as context so that it can't find the row like "_Top200[GameWeekRowNumber] = PreviousWeek". To get the correct result, you should change the context into entire table using ALL().

 

Previous Week Revenue = 
var PreviousWeek=MAX(_Top200[GameWeek])-1
return
CALCULATE(SUM(_Top200[Revenue]),FILTER(ALL(_Top200),_Top200[GameWeek]=PreviousWeek))

 

 

Regards,

BezosBuddy's avatar
BezosBuddy
Frequent Visitor
9 years ago

Thanks for your reply, Simon,

 

I've updated my measure as you've suggested, but by enveloping my table with the ALL function, I now get a sum of the entire dataset in this location, repeated for each game title in my data.. 

  • I should mention that I'm using a slicer to drill down on just one game title in my example above. 
  • If the only data I had was just these two rows, then your suggestion would work, but it doesn't work when I remove the filter and I have the full dataset in the table.

 

I need to get the sum of a game's revenue from the previous week, in order to calculate a week-over-week metric.