Forum Discussion
Display % between columns on matrix
Hello guys I have a matrix that look like this below
Country and office is a hierarchy.
the values are from = DISTINCTCOUNTNOBLANK(Data[ENumber])
Goal is to get the percentage between each year from 2019 to 2020 and 2020 to 2021.
| Country/Office | 2019 | 2020 | 2021 | Total |
| +Japan | 433 | 992 | 1740 | 2460 |
| +China | 1 | 8 | 44 | 48 |
| +Philippines | 9 | 9 |
Please guys I really need help I dont know if this is possible in power bi matrix
Thank you
Hi Kalachuchi
You can try these Measures.
values = DISTINCTCOUNTNOBLANK('Data'[Enumber])Change % = VAR res1 = CALCULATE ( [values], YEAR ( 'Data'[Year] ) = 2019 ) VAR res2 = CALCULATE ( [values], YEAR ( Data[Year] ) = 2020 ) VAR res3 = CALCULATE ( [values], YEAR ( Data[Year] ) = 2021 ) VAR res = SWITCH ( YEAR ( SELECTEDVALUE ( Data[Year] ) ), 2020, ( res2 - res1 ) / res2, 2021, ( res3 - res2 ) / res3 ) RETURN resThe result looks like this:
For more details, you can refer the attached pbix.
Best Regards
Caiyun Zheng
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Kalachuchi , You cave to create a measure with help from separate date or year table.
example
//Only year vs Year, not a level below
This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uAI am only getting 0 on all result
var ThisYear = CALCULATE(DISTINCTCOUNTNOBLANK(Data[ENumber]),filter(ALL('Data'),'Data'[Year]=max('Data'[Year]))) var LastYear = CALCULATE(DISTINCTCOUNTNOBLANK(Data[ENumber]),filter(ALL('Data'),'Data'[Year]=max('Data'[Year])-1)) var diff = divide([ThisYear]-[LastYear],[LastYear]) return diffI have a measure selection which gets the distinctcountnoblank(Data[Enumber]) to get the count of each year
Country/Office 2019 2020 2021 Total +Japan 433 992 1740 2460 +China 1 8 44 48 +Philippines 9 9 The year is in date format and is only 2021,2019 and 2020
Hi Kalachuchi
You can try these Measures.
values = DISTINCTCOUNTNOBLANK('Data'[Enumber])Change % = VAR res1 = CALCULATE ( [values], YEAR ( 'Data'[Year] ) = 2019 ) VAR res2 = CALCULATE ( [values], YEAR ( Data[Year] ) = 2020 ) VAR res3 = CALCULATE ( [values], YEAR ( Data[Year] ) = 2021 ) VAR res = SWITCH ( YEAR ( SELECTEDVALUE ( Data[Year] ) ), 2020, ( res2 - res1 ) / res2, 2021, ( res3 - res2 ) / res3 ) RETURN resThe result looks like this:
For more details, you can refer the attached pbix.
Best Regards
Caiyun Zheng
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
