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

How to calculate a percentage variation between years per category

Hi, 

 

I am beginner in Power Bi and I am stuck on this problem...

I would like to calculate the percentage of variation between years per category :

My data are : 

YearPercentageoftransportInaccidentfortheyearTransport

2020

15%A pied
20200%A Vélo
20208%Autre
20200%Moto
20200%Transport Public
202077%Voiture
201911%A Pied
20193%A Vélo
20193%Autre
20198%Moto
20194%Transport Public
201970%Voiture
201810%A Pied
20181%A Vélo
20181%Autre
20189%Moto
20180%Transport Public
201879%Voiture

 

I would like to see the variation between 2 years per category, for example :

TransportYear N-1Year NTrend% variation
Voiture70%77%up+7%
A Vélo3%0%down-3%
Autre3%8%up+5%

 

Thanks a lot for your help, AnneSo

2 REPLIES 2
vivran22
Community Champion
Community Champion

Hello @Anonymous ,

 

I have noticed that you have kudoed my response but not accepted it as a solution. Is there anything missing which can help in marking this post as a solution?

 

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

vivran22
Community Champion
Community Champion

Hello @Anonymous ,

 

You may use the following measures:

 

Avg Accident = AVERAGE(dtTable[Percentage])


% Transport Accident PY = 
VAR _PrevYear = 
    MAX(dtTable[Year])-1

VAR _PrevAvg = 
    CALCULATE(
        [Avg Accident],
        FILTER(
            dtTable,
        dtTable[Year] = _PrevYear
    )
    )
RETURN
_PrevAvg


% Transport Accident CY = 
VAR _CurrentYear = 
    MAX(dtTable[Year])

VAR _CurrentAvg = 
    CALCULATE(
        [Avg Accident],
        FILTER(
            dtTable,
        dtTable[Year] = _CurrentYear
    )
    )
RETURN
_CurrentAvg

Trend = 
    IF(
        [% Transport Accident CY] > [% Transport Accident PY],
        "Up",
        "Down"
    )

% Diff = [% Transport Accident CY] - [% Transport Accident PY]

 

Following is the result:

 

Capture.JPG

Cheers!
Vivek

If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)


Feel free to email me for any BI needs.
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

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.

Top Solution Authors