4

I have created groups of suppliers based on the invoice amount received per category, i.e. the suppliers responsible for the first 80% of amount received, 80-95% received and final 5% received. The data is sorted in descending order so that the biggest suppliers should occupy the first group where possible.

The calculated field is as follows: if running_Sum(sum([Invoiced Value Calculation]))/window_sum(sum([Invoiced Value Calculation])) < 0.8 then "First 80%" elseif running_Sum(sum([Invoiced Value Calculation]))/window_sum(sum([Invoiced Value Calculation])) < 0.95 then "80-95" else "Final 5" END

I have created accurate visuals which display the discrete suppliers but have so far been unable to group them. I just want an accompanying table that displays the number of suppliers per group rather than one row per supplier which is what I'm currently getting.

Table without groups

Extract of data

Output I am looking for

Nick
  • 41
  • 2
  • To confirm: the formula is working correctly and you are happy with the numbers in the shown table but you are looking for a summary table that outputs: First 80%: 3, 80-95: 2, Final 5: 6 – GivenX Oct 26 '22 at 11:49
  • ...and shows these totals for each Cost_Ca[tegory?] in your data (of which you only have "Film" in the example) – GivenX Oct 26 '22 at 12:04
  • Yes that's exactly right, I've edited the question to make it clearer what I am looking for. I've only included one category for now but there are multiple in the full dataset so need it summarised per category. – Nick Oct 26 '22 at 13:01
  • I cannot crack this but am sure someone else more capable than me will be able to figure it out... – GivenX Oct 26 '22 at 13:57
  • The typical solution to problems like these is write a (nested) table calculation that assigns the numbers you want to each item in the group, so like 3, 3, 3, 0,0,0,0, ... for your first column. But you don't want to display those 3s multiple times, so use a table calc filter (something like first()=0) to only show the first value in each calc window, and hide the rest. Sorry if this is cryptic, but time is short. The other choice is to use custom SQL and analytic SQL queries instead of table calcs to rank your suppliers – Alex Blakemore Oct 27 '22 at 19:10

1 Answers1

0

Click on the rows you wish to group, hover the selection, then click the group icon (looks like a paper clip); more detail here.

You could also use a count distinct of suppliers and add that to the text mark.

MUFF.
  • 178
  • 5