Calculation on rows for siblings

Hi Jesper

Do you need an average row underneath every organisation category? Or just a number of average rows in the bottom - one for each category? Im not aware of a possibility to use a new messure that works on rows instead of columns?

Could it be a solution to apply the average as a calculated column instead? Then you will have that the average will show in all the rows, but you can then fx just show the average in the first row of every category using the invisible agent?

If you need it as a row in the bottom, with an average per category, maybe you could do the calculation with absolute references (0,@"[Ungerådgivningen]";"[41306]",0), but I dont know how to change the second dimension from a specific number to "all". I dont think (0,@"[Ungerådgivningen]";all,0) will work.

Hopfully someone else have a better idea :-)

Hi Louise

Thanks for your reply

It has to be a row, so it can show an average on all future months.

I thought it was the absolute reference, @"[xxx]", I should use, but I have never used it - how does it work? Normaly the syntax is avg("Collum", "Row", "Measure"), but you are putting the organization in at second - how so?

I have triede with the absolute reference, both with a row calculation and column calculation without any luck. 

Hi Jesper

I have posted a tip & trick about the absolute referenced here: https://community.targit.com/hc/en-us/community/posts/360014784338-Absolute-Reference-Calculation

Have you tried to do at row calculation with the syntax: sum(0,@"[Ungerådgivningen]";"[41306]",0)

Then it should give you the same result as shown in the row where you have Ungerådgivningen and Borger ID 41306.

The first 0 is instead of d1 and the second 0 is instead of m1 - to make sure it will do the calculation for the column you are placed (the numbers above the calculation).

Does it make sense?

Im sure Ole or Niels have made a much better explanation to use the functionality some where? And hopefully they have an idea to get af sum of all the observations in the second dimension.

But this is just how much I know about it ;-D 

 

To your question: Normaly the syntax is avg("Collum", "Row", "Measure"), but you are putting the organization in at second - how so?

In your table you have Organisation and Borger ID as row dimensions, Year and month as Column dimensions and Numbers as your messure. That is why you have to point at your organisation category ind the second place of your syntax.

Hope it makes sence? 

Hi Jesper,

I can get calculated rows with averages within each main category (Asia, Europe and North America) like this:

First, I am adding the "Count children" calculated column. It can later be hidden, if necessary.

Count children:

allcount(d1, all(c), m1)

Then I am adding the calculated average rows.

sum(0, @"[Asia]"(l(1,0)), m1) / sum(c1, @"[Asia]"(l(1,0)), m1)

sum(0, @"[Europe]"(l(1,0)), m1) / sum(c1, @"[Europe]"(l(1,0)), m1)

sum(0, @"[North America]"(l(1,0)), m1) / sum(c1, @"[North America]"(l(1,0)), m1)

I hope it makes sense.

BR / Ole

Nice :-)

Thanks Ole, I can very much use this knowledge as well.

Have a nice weekend