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 correct; I'm left wondering why this value doesn't appear in the area enclosed by the red box:

 

 

Here's my formula for the measure:

PrevWeekRevenuesum = VAR PreviousWeek = CALCULATE(MAX(_Top200[GameWeekRowNumber])) - 1
RETURN
CALCULATE(SUMX(_Top200, _Top200[Revenue]), FILTER(_Top200, _Top200[GameWeekRowNumber] = PreviousWeek))

2 Replies

  • 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.