9 Replies Latest reply on Feb 25, 2016 1:27 AM by Dave Rawlings

# Level of Detail Calculation with a Running Sum

Hi,

I have an issue that I just don't seem to be able to find a way around.  I can't upload data as that isn't allowed where I work, which obviously makes this slightly more difficult.

I have a list of clients, with a client ID, and associated sales numbers.  I want to show which clients make up 80% of the overall revenue, and then count how many clients that is on a dashboard or other report.  There are other categories but they aren't causing an issue at the moment.

For something like this I would normally use a running sum and divide that by the total sales, so a calc that is something like if running sum/ total sales >=80% THEN "Top N" ELSE "" END.

And for other types of categorisations I would use a Fixed LOD at client ID level.

However I can't combine the 2 as you can't use a table calc within a LOD.  Can you think of any other way I could "stamp" each client with whether they make up 80 % of the revenue, in a way that I could then count those?

Hopefully this makes some sort of sense

Thanks

• ###### 1. Re: Level of Detail Calculation with a Running Sum

Anyone any ideas?

• ###### 2. Re: Level of Detail Calculation with a Running Sum

Hey Dave!

I would suggest uploading atleast a few sample rows of your data. Probably if there are other fields, the solution could hinder based on whats on the view. I am not sure running_sum would work because the values would change if the measure value is sorted vs. not sorted. Customers making up 80% of the total revenue would change I would assume.

Pooja.

• ###### 3. Re: Level of Detail Calculation with a Running Sum

Running sum works, as long as the clients are shown on screen, as soon as I try to create a summary table of the data the top 80% recalculates based on what is now shown on screen.  I'd like to stop that happening.

• ###### 4. Re: Level of Detail Calculation with a Running Sum

I'm not sure where you have trouble regarding LOD?

Here is a sample.

[Sales Share %]

{fixed [Client] :sum([Sales])}/{fixed : sum([Sales])}

Top 80% In/Out

if RUNNING_SUM(sum([Sales Share %]))<=0.8 then "IN" else "OUT" END

Thanks,

Shin

9.0 attached

• ###### 5. Re: Level of Detail Calculation with a Running Sum

Thanks I'll take a look.

In that data would you be able to produce a table that was in this format:

Top 80     Count of Clients     Sum of Sales

In               X                              £X

Out               X                              £X

• ###### 6. Re: Level of Detail Calculation with a Running Sum

That's a good question, let me try.

Thanks,

Shin

• ###### 7. Re: Level of Detail Calculation with a Running Sum

Thanks.  That's kind of where I'd like to get to and can't quite get there

• ###### 8. Re: Level of Detail Calculation with a Running Sum

OK, finally done.

## But quite a bit tricky.

Need to use window_sum, window_count and use "hide header" technique.

The point here is we cannot remove "Client" from the Marks because this is the key to determine the Top 80%, IN/OUT.

So we need to show a certain client's line as a representative of each category.

Then flag that client and hide others.

These are story line of the whole procedure.

Put [Client] in Marks and sort

Create calculated filed and set Set Default as below.

[window_sales]

case [Top 80% In/Out]

when "IN" then window_sum(if [Top 80% In/Out]="IN" then sum([Sales]) end)

when "OUT" then window_sum(if [Top 80% In/Out]="OUT" then sum([Sales]) end)

END

[Window_count]

case [Top 80% In/Out]

when "IN" then window_count(if [Top 80% In/Out]="IN" then count([Client]) end)

when "OUT" then window_count(if [Top 80% In/Out]="OUT" then count([Client]) end)

END

Put two fields into Measure Values and edit table calculation

One more calculated field

[Client Flag]

IF First()=0 OR [Top 80% In/Out]!=lookup([Top 80% In/Out],-1)

THEN [Window_Sales]

ELSE NULL

END

Then hide Null from Client Flag. (Not exclude, just hide)

Enjoy !!!!

Thank you ,

Shin

9.0 attached.

1 of 1 people found this helpful
• ###### 9. Re: Level of Detail Calculation with a Running Sum

Thanks, that looks great