Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-eqin-msft
Community Support
Community Support

Hi @Anonymous ,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-JtUoxehwBXtWkWNyyVXEql_1gjPNM0g?e=8bCLoo 

 

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

v-eqin-msft
Community Support
Community Support

Hi @Anonymous ,

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

Anonymous
Not applicable

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

amitchandak
Super User
Super User

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.