Forum Discussion

ttankka's avatar
ttankka
Frequent Visitor
5 years ago
Solved

Total measure - need help

Hi All,
I am having an issue with measure totals and like many others I am not able to make it work.
I have a revenue table joined with dates.

Here is a table

and a matrix with current and previous period and date range 7/1/20 - 9/30/20

 

mRevThis = sum(Sheet1[revenue])
mRevPrev = CALCULATE(sum(Sheet1[revenuePrev]),filter(Sheet1,sum(Sheet1[revenue]) <> 0))
Only show mRevPrev if there was revenue for mRevThis and then group it all for the date range.
i.e. cust 22222 has revenue for 7/1/20 of $89 but total all revenue for previous period $844 + $153 = $997.  This is correct.
 
I get the expected value at the row level but have tried many summary measures for the total to no avail.
I will ultimately base more measures of off the total and they will end up in charts.
Help with the the total on mRevPrev will be greatly appreciated.
 
Thank you,
Tim

 

 

Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    Have you tried this expression?

     

    NewMeasure = SUMX(VALUES(Sheet1[Customer]), [mRevPrev])

     

    Pat

     

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    Have you tried this expression?

     

    NewMeasure = SUMX(VALUES(Sheet1[Customer]), [mRevPrev])

     

    Pat

     

    • ttankka's avatar
      ttankka
      Frequent Visitor
      5 years ago

      Hi Pat,

      Yes, it works at this level.  Thank you!  I still need to apply this measure to other calculations and I hope to get expected results.

      Tim