Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
All,
I have ten companies in 6 different straight table that are the top 10 companies with the highest spend for 6 different divisions. I would like to add a text box below to display the percentage these to companies make up of the spend as a total per that division - getting caught up on the rank function. Any ideas?
Few things I've tried:
=num(Sum({<[Spend Division]={'A'},[Spend Division]={"={rank(sum([2015 Spend]))<=10"}>}[2015 Spend])/sum({<[Spend Division]={'A'}>}TOTAL [2015 Spend]),'%#,##0.00')
=num(
(sum(aggr(if(rank(sum({<[Spend Division] = {'A'}>} [2015 Spend]))<= 10 ,sum({<[Spend Division] = {'A'}>} [2015 Spend])),[Spend Division]))) /
sum({<[Spend Division] = {'A'}>} [2015 Spend]),'%#,##0.00')
May be this:
=Num(Sum({<[Spend Division]={'A'}, CompanyID = {"=Rank(Sum({<[Spend Division]={'A'}>}[2015 Spend]))<=10"}>} [2015 Spend])/Sum({<[Spend Division]={'A'}>} [2015 Spend]),'%#,##0.00')
or
=Num(Sum({<[Spend Division]={'A'}, CompanyID = {"=Rank(Sum({<[Spend Division]={'A'}>}[2015 Spend]), 1, 1)<=10"}>} [2015 Spend])/Sum({<[Spend Division]={'A'}>} [2015 Spend]),'%#,##0.00')
May be this:
=Num(Sum({<[Spend Division]={'A'}, CompanyID = {"=Rank(Sum({<[Spend Division]={'A'}>}[2015 Spend]))<=10"}>} [2015 Spend])/Sum({<[Spend Division]={'A'}>} [2015 Spend]),'%#,##0.00')
or
=Num(Sum({<[Spend Division]={'A'}, CompanyID = {"=Rank(Sum({<[Spend Division]={'A'}>}[2015 Spend]), 1, 1)<=10"}>} [2015 Spend])/Sum({<[Spend Division]={'A'}>} [2015 Spend]),'%#,##0.00')
Boom, thanks Sunny!