topic Aggregating in a specific part of a chart in App Development

Aggregating in a specific part of a chart

mcsmithums1 — Thu, 25 Apr 2024 13:13:53 GMT

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?

Re: Aggregating in a specific part of a chart

Anil_Babu_Samineni — Fri, 26 Apr 2024 04:21:04 GMT

Perhaps this?

Sum(Athletes_money)/sum({<Sports={'athletes'}>}total Athletes_money)

Sum(Athletes_money)/sum(total <FieldName> Athletes_money)

Re: Aggregating in a specific part of a chart

Vegar — Fri, 26 Apr 2024 04:38:16 GMT

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.

Re: Aggregating in a specific part of a chart

mcsmithums1 — Fri, 26 Apr 2024 17:46:37 GMT

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.

Re: Aggregating in a specific part of a chart

Vegar — Sun, 28 Apr 2024 19:24:34 GMT

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))