Forum Discussion

powerbinovice01's avatar
powerbinovice01
Frequent Visitor
2 years ago

Hello Greg,
Thank you for your quick response. I can't believe I watched one of your videos earlier and you are the one to help me with a solution!
Real qucik for clarification purposes, my weekly sales are currently summarized. Are you suggesting I create a new measure, something like Weekly Sales= (SumX('Weekly Sales' [Weekly Sales])
Then proceed and create a new formula for my Difference between Current Week and Prior Week something along the lines of Diff TW and LW = SumX('Weekly Sales',
IF(ISBLANK('Weekly Sales'[Weekly Netchange]) || ISBLANK('Weekly Sales'[Last Week Sales]),
BLANK(),'Weekly Sales'[Weekly Netchange] - 'Weekly Sales'[Last Week Sales]))?


Thank you for your clarification! 

Greg_Deckler's avatar
Greg_Deckler
Community Champion
2 years ago

powerbinovice01 With what I am picturing in my head, it should be something along the lines of this:

 

Total Weekly Difference =
  VAR __Table = 
    SUMMARIZE(
      'Weekly Sales',
      [Weekly Report Date],
      [Vendor Name],
      "Diff", [Diff Between Weekly TW and LW]
    )
  VAR __Result = IF( HASONEVALUE('Weekly Sales'[Weekly Report Date]), [Diff Between Weekly TW and LW], SUMX( __Table, [Diff] )
RETURN
  __Result

 

You would use this measure in your visual instead of the "Diff Between TW and LW" measure. For rows, it simply returns the Diff Between TW and LW measure value. But for the total row, it will return the summed value across the virtual table which should be summarized exactly the same as your visual summarizes the data.