cancel
Showing results for
Did you mean:
Highlighted
Helper V

## Calculate change in measure value period vs. period change

I have a division measure that takes Delivery cost divided by a Sales Cost to arrive at a %. I want to calculate the change in % points period vs. period. How do I do this? It feels like I need a measure of a measure?

Using the below example.

Period 1 measure result is 15.1%.

Period 2 measure result is 11.9%

I want to subtract the two periods 15.1% - 11.9% = 3.2%

The visual right now is looking at Quarter 1, but I will eventually make more visuals that may display the full year, one month, or a week. So, I would like to this to be dynamic.

Note: here is the measure I'm currently using:

CA Amt (Allocated) divided by Delivery USD =
DIVIDE(
SUM('Deliveries'[CA Amt (Allocated)]),
SUM('Deliveries'[Delivery USD])
)

4 REPLIES 4
Highlighted
Super User IX

Highlighted
Microsoft

## Re: Calculate change in measure value period vs. period change

Hi @jcueland ,

According to my understanding ,you want to calculte the percentage's change based on the same day/month/quarter between years,right?

Here is my data sample:

Then try to use the following formula:

``````divide values =
DIVIDE (
CALCULATE (
SUM ( 'Delivery date'[Value] ),
FILTER ( 'Delivery date', 'Delivery date'[CA&USD] = "CA" )
),
CALCULATE (
SUM ( 'Delivery date'[Value] ),
FILTER ( 'Delivery date', 'Delivery date'[CA&USD] = "USD" )
)
)``````

////Year-Quarter for matrix column

``````year-quarter =
'Delivery date'[Date].[Year] & " " & 'Delivery date'[Date].[Quarter]``````
``````totalResults =
IF (
HASONEVALUE ( 'Delivery date'[year-quarter] ),
IF (
HASONEVALUE ( 'Delivery date'[CA&USD] ),
SUM ( 'Delivery date'[Value] ),
[divide values]
),
CALCULATE (
[divide values],
FILTER (
'Delivery date',
'Delivery date'[Date].[Year] = 2019
&& 'Delivery date'[Date].[QuarterNo] = 1
)
)
- CALCULATE (
[divide values],
FILTER (
'Delivery date',
'Delivery date'[Date].[Year] = 2020
&& 'Delivery date'[Date].[QuarterNo] = 1
)
)
)
``````

My visualizations look like this:

Is the result what you want? If not, please upload some data samples and expected output.

Best Regards,
Eyelyn Qin

Highlighted
Helper V

## Re: Calculate change in measure value period vs. period change

Thanks so much. I think yours might work but I'm wondering if there is a simplier approach?

I've taken your example data set and created an example model with link below. I need to add one additional column to show what I was trying to accomplish.

https://1drv.ms/u/s!AvCirXUmRp-JhbY7bLCDvl6fm6HZNA?e=gPcpmv

Using example data set I'm trying to subtract 2.67% (Measure from Q1) - 0.30% (Measure from Q2) = -2.07% difference in quarters

Highlighted
Microsoft

## Re: Calculate change in measure value period vs. period change

1. Select CA and USD columns and then Use "unpivot" in Query Editor,then data will be transformed like this:

2. Use the formula I refered before,then the output looks like this:

Best Regards,

Eyelyn Qin

Announcements

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors