Find Difference Between Rows in Table based on criteria

[Anonymous]

I am trying to calculate the total punches percent change compared to the punches for 3/15/20, for each date for each subvertical. I've included an example of the data below, the last column is what I am trying to calculate. So for the first row, I need the formula to be pulling 981,000/987,000 and anytime the date is 3/15/20 it should be showing 100%. Can anyone help me?

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can create a measure as below:

Difference from 3/15 = 
VAR _punchonMar15 =
    CALCULATE (
        SUM ( 'Punch info'[Total Punches] ),
        'Punch info'[Date] = DATE ( 2020, 3, 15 )
    )
RETURN
    IF (
        MAX ( 'Punch info'[Date] ) = DATE ( 2020, 3, 15 ),
        1,
        1
            - DIVIDE ( _punchonMar15 - SUM ( 'Punch info'[Total Punches] ), _punchonMar15 )
    )

Best Regards

Rena

[Anonymous]

Not sure if I'm supposed to start a new post or not but is there a way to add a 'total' line to the chart now that will just sum whatever lines I'm showing? 

[Anonymous]

Hi @Anonymous ,

You can follow the below steps to get the total line:

1. Select the related visual, navigate to "Format" pane and turn on the option "Totals"  

2. Create another measure with below formula in order to get the correct total value, then put this new measure on the visual to replace the orignal measure "Difference from 3/15"

Measure 2 = SUMX(VALUES('Punch info'[Sub Vertical]),[Difference from 3/15])

Best Regards

Rena

[Anonymous]

Sorry, I think I should have been more specific... I have this in a line graph, with percent on the x axis and subvertical on the y. I want to add a line for a total of the subverticals - so summing up all the punches and then calculating the difference from 3/15/20...

[Anonymous]

Thank you!! This worked perfectly and the visual really helped!!

[mahoneypat]

Not sure if you are looking for a column or measure expression, so you can try this expression in a measure, replacing Table with your actual table name.

Difference from March15 =
VAR __sumthisdate =
    SUM ( Table[Total Punches] )
VAR __sumMarch15 =
    CALCULATE (
        SUM ( Table[Total Punches] ),
        ALL ( Table[Date] ),
        Table[Date] = DATE ( 2020, 3, 15 )
    )
RETURN
    DIVIDE ( __sumthisdate - __sumMarch15, __sumMarch15 )

For the measure version, remove the SUM in the first variable (just Table[Total Punches]) and change the ALL in the 2nd variable to ALLEXCEPT(Table, Table[Sub Vertical])

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat