Calculate difference in measure between category vs grand total change over time (.pbix attached)

[Anonymous]

I have a measure that calculates the % of two other columns (it is Freight as a % of sales). I want to calculate how this has changed over time and difference of the categories versus the grand total from a prior period. I'm unclear how to do this.

I have attached an example data set as well as a picture below of what I'm trying to do.

Example

Category A is 1.2% in 2020

The Grand Total in 2019 was 0.38%

The difference between Category A in 2020 vs. the Grand Total is 0.35% - 1.20% = -0.85%.

How do I calculate this difference? 

Example data in PowerBI: https://1drv.ms/u/s!AvCirXUmRp-JhbY86LW-Vr-skR6XPw?e=AmqJYb

[v-deddai1-msft]

Hi @Anonymous ,

Please refer to the measure below:

Measure =
VAR lastyear =
    CALCULATE (
        [Freight % of Sales],
        ALL ( 'Table'[Category] ),
        SAMEPERIODLASTYEAR ( DateTable[Date] )
    )
RETURN
    lastyear - [Freight % of Sales minus % Change Difference]

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

Best Regards,

Dedmon Dai

[mahoneypat · Accepted solution]

Please try this expression to get your desired result.

NewMeasure =
VAR thisyear =
    MIN ( DateTable[Year] )
VAR freightpctsalesminus = [Freight % of Sales minus % Change Difference]
VAR PYtotal =
    CALCULATE (
        [Freight % of Sales],
        ALL ( 'Table'[Category] ),
        ALL ( DateTable ),
        VALUES ( DateTable[Quarter] ),
        DateTable[Year] = thisyear - 1
    )
RETURN
    PYtotal - freightpctsalesminus

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

Regards,

Pat