Forum Discussion
Solved
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. Year Q1 Q2 ...
seems straightforward. What happened to 2022 ?
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
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.
