cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
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])
)

 

measure change.png

 

4 REPLIES 4
Highlighted
Super User IX
Super User IX

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

Highlighted
Microsoft
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:

2.PNG

 

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:

1.jpg

Is the result what you want? If not, please upload some data samples and expected output.
Please do mask sensitive data before uploading.

 

Best Regards,
Eyelyn Qin

Highlighted
Helper V
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

example measure.png

Highlighted
Microsoft
Microsoft

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

Hi @jcueland ,Please follow these steps:

 

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

8.10.follow.1.PNG

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

8.10.follow.2.PNG

Here is my pbix link:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/stephent01_qiuyunus_onmicrosoft_com/EZvMgo6bBmhFvf... 

 

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

 

Best Regards,

Eyelyn Qin

Helpful resources

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