Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have created a bar chart with data that drills down. I am having problems with calculations in that chart. There are two filters, the bigger one is sports and the smaller one is athletes. Given the 5 biggest athletes in a few major sports, I want the chart to show a % of pay each of those athletes gets out of the sum of all 5 athletes.
My current code looks something like this:
Sum(Athletes_money)/sum(total Athletes_money)
This works when a specific sport is chosen, but when no filter is chosen and the bar chart shows all sports separate, the individual athletes money is divided by all of the athletes that are in the filter. Is it possible to make the denominator the total of just the athletes sports grouping instead of all athletes?
Perhaps this?
Sum(Athletes_money)/sum({<Sports={'athletes'}>}total Athletes_money)
Or
Sum(Athletes_money)/sum(total <FieldName> Athletes_money)
I agree with Anil, that
Sum(Athletes_money)/sum(total <Sport> Athletes_money)
could be what you are looking for.
The denominator will be the total Athletes money per sport. Important that the Sport dimension is included in your chart.
Definitely helpful and what I was looking for thank you. Now that that data is working in my graph, how can I create a kpi that sums up all of the percentages? This number should be 100% times the number of sports, but it is helpful to me. If I copy and paste this code into a KPI it is giving the wrong number.
Consider this script
LOAD * inline [
ID, Sport, Athletes_money
1,Tennis, 10
2,Tennis, 10
3,Tennis, 10
4,Tennis, 10
5,Fotball, 20
6,Fotball, 20
7,Fotball, 20
8,Fotball, 20
9,Fotball, 20
10,Figure skating, 30
11,Figure skating, 30
];
Then you can calculate the precentage of sport on row level, but aggregate it to the total using sum (aggr()) like this sum(aggr(sum(Athletes_money)/sum(total <Sport> Athletes_money),Sport,ID))