Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Experts.
I'm facing an issue(see attached jpeg):
I've created a straight table with on dimension
The table has the following Columns: C, A(Simple sum), B(simple Sum) and A/B which shows the ration between A and B,
Now, I wanted to show the top 10 of column C so I used the Dim limitation and set it on fix 10.
After doing so, the Total Remained the same(meaning calculating sum for all the products). Changing the Total setting per column from auto to sum fixed the problem except for the percentage. I cant use it on column A/B because it shows a simple Total of percentage and not the percentage of the Total row.
In the example attached, the Total Setting in column A/B is Auto and show 94.6%. The Sum I need Is 57.4%
Regards,
Motty
Youc an achieve what u want using the rank() function.
I'll be creatiung dummy data to show how to do it (and please, next time attach some dummu data)
Let assume I have this:
Now I want to show the top 2.
To see the rank of each RowNo based on its sum(Value), we use the Rank() fucntion:
rank(sum({<RowNo>}Value),RowNo)
Now I want to show the top 2 RowNo based on the sum(Value) which means the 2 dimensions with rank <=2 : RownNo 4 and 5
Expression 1 (sum): Sum({<RowNo={"=rank(sum({<RowNo>}Value),RowNo)<=2"}>}Value)
Expression 2 (%) : Sum({<RowNo={"=rank(sum({<RowNo>}Value),RowNo)<=2"}>}Value)/ sum(total {<RowNo>} Value)
Result:
Solved Using Calculated dimension
=aggr(if(rank(C)<=10,Product),Product)
u're welcome...