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
sanc_152
Helper I
Helper I

Percentage calculation

Hi All, 

 

Please find the below sanpshot.I have a data with this format.Based on that i want to calculate the percentage over each year quarter how much it is showing differnce.

YearQ1Q2Q3Q4
202040424445
202156707172
202345464748
2024    

 

result:

YearQ1Q2Q3Q4
20200000
2021-0.440.33333-0.61364-0.6
20230.1964290.3428570.3380280.333333
2024    

 

inside the first line the result should be 0 as it showing in the above result since there no year for the comparision.for eg year 2o2o do not have any previous year comparision.

 

@amitchandak  ,

 

can you please help on  above scenario.

2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

seems straightforward.  What happened to 2022 ?

 

lbendlin_0-1675540853524.png

 

View solution in original post

v-binbinyu-msft
Community Support
Community Support

Hi @sanc_152 ,

Please try below steps:

1. create measure with below dax formula

Percentage Q1 =
VAR cur_year =
    SELECTEDVALUE ( 'Table'[Year] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Year] < cur_year )
VAR pre_year =
    MAXX ( tmp, [Year] )
VAR _a =
    CALCULATE ( MAX ( 'Table'[Q1] ), 'Table'[Year] = cur_year )
VAR _b =
    CALCULATE ( MAX ( 'Table'[Q1] ), 'Table'[Year] = pre_year )
VAR _val =
    DIVIDE ( _a - _b, _b )
RETURN
    IF ( ISBLANK ( pre_year ) || ISBLANK ( _a ), 0, _val )
Percentage Q2 =
VAR cur_year =
    SELECTEDVALUE ( 'Table'[Year] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Year] < cur_year )
VAR pre_year =
    MAXX ( tmp, [Year] )
VAR _a =
    CALCULATE ( MAX ( 'Table'[Q2] ), 'Table'[Year] = cur_year )
VAR _b =
    CALCULATE ( MAX ( 'Table'[Q2] ), 'Table'[Year] = pre_year )
VAR _val =
    DIVIDE ( _a - _b, _b )
RETURN
    IF ( ISBLANK ( pre_year ) || ISBLANK ( _a ), 0, _val )
Percentage Q3 =
VAR cur_year =
    SELECTEDVALUE ( 'Table'[Year] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Year] < cur_year )
VAR pre_year =
    MAXX ( tmp, [Year] )
VAR _a =
    CALCULATE ( MAX ( 'Table'[Q3] ), 'Table'[Year] = cur_year )
VAR _b =
    CALCULATE ( MAX ( 'Table'[Q3] ), 'Table'[Year] = pre_year )
VAR _val =
    DIVIDE ( _a - _b, _b )
RETURN
    IF ( ISBLANK ( pre_year ) || ISBLANK ( _a ), 0, _val )
Percentage Q4 =
VAR cur_year =
    SELECTEDVALUE ( 'Table'[Year] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Year] < cur_year )
VAR pre_year =
    MAXX ( tmp, [Year] )
VAR _a =
    CALCULATE ( MAX ( 'Table'[Q4] ), 'Table'[Year] = cur_year )
VAR _b =
    CALCULATE ( MAX ( 'Table'[Q4] ), 'Table'[Year] = pre_year )
VAR _val =
    DIVIDE ( _a - _b, _b )
RETURN
    IF ( ISBLANK ( pre_year ) || ISBLANK ( _a ), 0, _val )

2. add a table visual with field and measure

vbinbinyumsft_0-1675650437227.png

Please refer the attached .pbix file.

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-binbinyu-msft
Community Support
Community Support

Hi @sanc_152 ,

Please try below steps:

1. create measure with below dax formula

Percentage Q1 =
VAR cur_year =
    SELECTEDVALUE ( 'Table'[Year] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Year] < cur_year )
VAR pre_year =
    MAXX ( tmp, [Year] )
VAR _a =
    CALCULATE ( MAX ( 'Table'[Q1] ), 'Table'[Year] = cur_year )
VAR _b =
    CALCULATE ( MAX ( 'Table'[Q1] ), 'Table'[Year] = pre_year )
VAR _val =
    DIVIDE ( _a - _b, _b )
RETURN
    IF ( ISBLANK ( pre_year ) || ISBLANK ( _a ), 0, _val )
Percentage Q2 =
VAR cur_year =
    SELECTEDVALUE ( 'Table'[Year] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Year] < cur_year )
VAR pre_year =
    MAXX ( tmp, [Year] )
VAR _a =
    CALCULATE ( MAX ( 'Table'[Q2] ), 'Table'[Year] = cur_year )
VAR _b =
    CALCULATE ( MAX ( 'Table'[Q2] ), 'Table'[Year] = pre_year )
VAR _val =
    DIVIDE ( _a - _b, _b )
RETURN
    IF ( ISBLANK ( pre_year ) || ISBLANK ( _a ), 0, _val )
Percentage Q3 =
VAR cur_year =
    SELECTEDVALUE ( 'Table'[Year] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Year] < cur_year )
VAR pre_year =
    MAXX ( tmp, [Year] )
VAR _a =
    CALCULATE ( MAX ( 'Table'[Q3] ), 'Table'[Year] = cur_year )
VAR _b =
    CALCULATE ( MAX ( 'Table'[Q3] ), 'Table'[Year] = pre_year )
VAR _val =
    DIVIDE ( _a - _b, _b )
RETURN
    IF ( ISBLANK ( pre_year ) || ISBLANK ( _a ), 0, _val )
Percentage Q4 =
VAR cur_year =
    SELECTEDVALUE ( 'Table'[Year] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Year] < cur_year )
VAR pre_year =
    MAXX ( tmp, [Year] )
VAR _a =
    CALCULATE ( MAX ( 'Table'[Q4] ), 'Table'[Year] = cur_year )
VAR _b =
    CALCULATE ( MAX ( 'Table'[Q4] ), 'Table'[Year] = pre_year )
VAR _val =
    DIVIDE ( _a - _b, _b )
RETURN
    IF ( ISBLANK ( pre_year ) || ISBLANK ( _a ), 0, _val )

2. add a table visual with field and measure

vbinbinyumsft_0-1675650437227.png

Please refer the attached .pbix file.

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-binbinyu-msft 

 

Thank You so much for your reply.

 

Can you please help me on below scenario.

It is the same case but data granularity Is differnt.

YearAttributeValue

2020Q140
2020Q242
2020Q344
2020Q445
2021Q156
2021Q270
2021Q371
2021Q472
2023Q145
2023Q246
2023Q347
2023Q448

and the expected result ouput :

YearQ1Q2Q3Q4
2020-0.047619-0.04545  
2021-0.2-0.01408  
2023-0.02173910.978723  
2024    

quarter on quarter comparision on same year quarter.for eg q2 vs Q1 of 2021 and more on

can you please help on this ..

@v-binbinyu-msft 

@amitchandak 

Thanks for your Superb reply !

help with one query here 

FILTER ( ALL ( 'Table' ), 'Table'[Year] < cur_year )

how is the all function work here in filter function..so confused ...
@v-binbinyu-msft 

lbendlin
Super User
Super User

seems straightforward.  What happened to 2022 ?

 

lbendlin_0-1675540853524.png

 

can you please send PBIX for mentioned logic ...I

 

@lbendlin 

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
Top Kudoed Authors