Hi,
I 've got data like this :
Table_Test_:
LOAD * INLINE [
ID, Year, Income, Expense
1 , 2014 , 987, 700
1 , 2015 , 1000, 900
1 , 2016 , 1100, 950
1 , 2017 , 1200, 900
2 , 2014 , 1800, 101
2 , 2015 , 2000, 1000
2 , 2016 , 1200, 50
2 , 2017 , 1900, 1700
3 , 2014 , 55, 1010
3 , 2015 , 900, 1000
3 , 2016 , 1200, 1050
3 , 2017 , 1210, 1070
4 , 2014 , 987, 986
4 , 2015 , 1111, 999
4 , 2016 , 1234, 955
4 , 2017 , 1212, 999
];
In a chart I've Year as dimension and Avg({$<Year=>}Income) & Avg({$<Year=>}Expense) as measures.
In the title I would like to have the year with the second % of exepense, the % of the expense and the % of the income.
For the 2 fisrt, that works fine, but i need help for the last one ...
='2nd Year less exp: ' & only(if(aggr(
Rank(sum(Expense)/Sum(Total Expense),Year)
,Year)=2,Year
)
)
& ' with ' &
round (max(aggr(sum(Expense)/Sum(Total Expense),Year),2) * 100, 0.1)
& '% expense and ' &
sum({$<Year ={'only(if(aggr(
Rank(sum(Expense)/Sum(Total Expense),Year)
,Year)=2 ,Year))
'} >
}Income)
/
Sum (Total {$<Year =>} Income) & '% income'
