Calculation on rows for siblings

• 

  Louise Stub Hansen

  April 15, 2021 09:40

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

  0
• 

  Jesper Bøgekvist

  April 16, 2021 10:41

  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. 

  0
• 

  Louise Stub Hansen

  April 16, 2021 11:16

  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 

   

  0
• 

  Louise Stub Hansen

  April 16, 2021 11:34

  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? 

  0
• 

  Ole Dyring

  April 16, 2021 11:37 (Edited April 16, 2021 11:41)

  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

  1
• 

  Louise Stub Hansen

  April 16, 2021 11:52

  Nice :-)

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

  Have a nice weekend

  0

Please sign in to leave a comment.