Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Find Difference Between Rows in Table based on Criteria

I am trying to calculate the percent change compared to the data for 3/15/20, for each date for each subvertical. Here is an example of the data. The last column is what I am trying to calculate. So ...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario.

    Table:

     

    You may create a measure as below.

    Difference from 3/15 = 
var _denominator = 
CALCULATE(
    SUM('Table'[Total Punches]),
    FILTER(
        ALL('Table'),
        'Table'[Sub Vertical] = SELECTEDVALUE('Table'[Sub Vertical])&&
        'Table'[Date] = DATE(2020,3,15)
    )
)
return
SELECTEDVALUE('Table'[Total Punches])/_denominator

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

lbendlin's avatar
lbendlin
Super User
6 years ago

try this one:

 

Difference = 
var s = SELECTEDVALUE(Table[Sub Vertical])
var tp315 = CALCULATE(sum(Table[Total Punches]), Table[Sub Vertical]=s, Table[Date]=date(2020,3,15))
return divide(sum(Table[Total Punches]),tp315)