0

I have a scenario where my data source already contains a running total by week field. This is helpful, however if I have this data in powerpivot/tabular, the sum of that field is not accurate. Is there a trick or technique that will allow me to still sum on this field to get grand total as a measure? Thanks for any recommendations. A sample data set is below, where the I would expect to see a grand total of 1334.36 instead 6382.02.

enter image description here

Date Week Sales 4/22/2016 1 273.07 4/29/2016 2 421.16 5/6/2016 3 575.14 5/13/2016 4 718.86 5/20/2016 5 871.92 5/27/2016 6 1015.57 6/3/2016 7 1171.94 6/10/2016 8 1334.36

user1134307
  • 218
  • 2
  • 5
  • 16

1 Answers1

0

Since the value is already a running total, I would just create a measure to grab the max value thusly:

Running Total:=MAX(tablename[Sales])

Jeremy Firth
  • 41
  • 4
  • I think that may be the right direction. What if there were another field called customer, and the week numbers repeated for each customer. Is there a dax max statement that could take that into consideration? – user1134307 Jul 20 '16 at 01:41
  • That max statement should still work in context. You will need a date table, and map your date column to the date table. You should then be able to drag your Month or Week or whatever column from your Date table you want to organize by into the Columns area of a pivot table, and drag the Customers field into Rows area of a pivot table, then drag the measure above into the Values field. – Jeremy Firth Jul 22 '16 at 16:45