Calculate Percentage of Multidimensional columns

[vidyasagar159]

Hello Community,

I want to create a Percentages based on the range of Amount by Year and Company. Please find the details below. I tried a couple of approaches but I am not able to achieve the expected result.

Actual Table:

Year	Company	Amount
2010	Apple	10
2010	Nokia	20
2010	Samsung	30
2010	Apple	100
2010	Nokia	200
2010	Apple	500
2011	Nokia	100
2011	Samsung	200
2012	Apple	500
2012	Nokia	150
2014	Apple	600
2014	Nokia	100
2014	Samsung	150
2014	Apple	300
2014	Nokia	200

Result Table: This is giving me the percentages of all the years.

Year	Company	Plan Size	Total Amount	Percentages
	Totals		3160	100%
2010	Apple	Greate than 500	610	19%
	Nokia	50-700	220	7%
	Samsung	Less then 50	30	1%
2011	Nokia	100-200	100	3%
	Samsung	100-200	200	6%
2012	Apple	50-700	500	16%
	Nokia	100-200	150	5%
2014	Apple	Greate than 500	900	28%
	Nokia	50-700	300	9%
	Samsung	100-200	150	5%

Dimension Expression:

Plan Size:

if(aggr(sum(Amount),Year,Company)<50,'Less then 50',

if(aggr(sum(Amount),Year,Company)>=100 and aggr(sum(Amount),Year,Company)<=200 ,'100-200',

if(aggr(sum(Amount),Year,Company)>500,'Greate than 500',

if(aggr(sum(Amount),Year,Company)>50 and aggr(sum(Amount),Year,Company)<700 ,'50-700',

))))

Measure Expression:

Total Amount:

(sum(total<Year,Company>Amount))

Percentage Amount:

sum(Amount)/(sum(total{$<Year,Company>}Amount))

But the Expected Result I want is to show the 100% split per each year.

Expected Result:

Year	Company	Plan Size	Total Amount	Percentages
2010	Apple	Greate than 500	610	71%
2010	Nokia	50-700	220	26%
2010	Samsung	Less than 50	30	3%
	Total		860	100%
2011	Nokia	100-200	100	33%
2011	Samsung	100-200	200	67%
	Total		300	100%
2012	Apple	50-700	500	77%
2012	Nokia	100-200	150	23%
	Total		650	100%
2014	Apple	Greate than 500	900	67%
2014	Nokia	50-700	300	22%
2014	Samsung	100-200	150	11%
	Total		1350	100%

Also, I will convert this table to 100% Stacked bar color by PLAN SIZE. So that I can see how much percentage of plan size for each year.

Thanks in Advance,

-Vidya

[sunny_talwar]

But then why not just do like how he is doing if we have to use Aggr()

[sunny_talwar]

Meaning this

Sum(Amount)/Aggr(nodistinct sum(Amount),Year).

[OmarBenSalem]

I havnt seen the full discussion.. my bad !

Well, It's 10 pm in France.. so, I'm doing this out of passion; can't blame me if I don't read it all

[vidyasagar159]

So, is there any difference between the 2 Expressions? Like performance optimization?

Thanks,

-Vidya

[vidyasagar159]

Guys. In the first place, i am really thankful to all of you for looking into this.

[sunny_talwar]

From what I see your expression should be slightly better performing because you are aggregating over a single dimension. But I havn't done testing to prove it. May be you can test to see which one works better

[vidyasagar159 · Accepted solution]

So, With all the respect and information provided by the gentlemen in this thread. Both the expressions will give the right output.

Measure Expressions:

Sum(Amount)/Aggr(nodistinct sum(Amount),Year)

OR

sum(Amount) / aggr(sum(total <Year> Amount),Year,Company)

Since my report consuming more than 10 Million records of data. I have to test which is the better one in terms of

performance.

Thanks,

-Vidya