8 Replies Latest reply on Sep 29, 2017 1:28 PM by Jennifer VonHagel

# How to create moving % of usage?

So I'm making a dashboard for the usage of my company's website. We're just trying to track if they've used certain features or not in a given week, month, year. So I created a count that says if a usage event occurs more than once then 1 if not 0. But now I'm trying to get a percentage of that count out of the total possible in that date range. For example if in the month of January there are 5 possible weeks. Each one is counted as 1 but the usage events happened only in two of those weeks it would 2/5. Ideally would like this to change depending on the date fields so could do days out of weeks, weeks out of months, days out of months etc. So for the below example. Holdings search would be calculated at 1/5, insights page view at 3/5, page view 3/15 and so and so forth. If anyone has any idea on how to even begin to approach this it would be much appreciated. I've been trying to figure this out for days and feel like I'm losing my mind ha.

• ###### 1. Re: How to create moving % of usage?

Pl attach a workbook

• ###### 2. Re: How to create moving % of usage?

Hi Ben, right-click on your measure, choose Quick Table Calculation > Percent of Total. Then right-click on your measure again and choose Compute Using > Table Across.

And we have this:

If you also want the actual number on that, you can put another copy on the marks card. Drag Sales (or your measure) to the Text box

Then I usually hit the Show Me tab and choose the table to quickly format things:

Then I re-arrange the columns in the way that makes best sense, and :

Hope this helps,

Jennifer

• ###### 3. Re: How to create moving % of usage?

Hi Jennifer,

Thanks for your reply. Unfortunately, I tried that and run into this issue. As the count itself is already a calculation on the event itself it just shows a 100%. Any other ideas?

• ###### 4. Re: How to create moving % of usage?

Hi Deepak,

Unfortunately I cannot upload a workbook as the underlying data is company sensitive. Best I can do is the screenshot of the formula and the description of what I'm trying to do from my initial post.

• ###### 5. Re: How to create moving % of usage?

Hmmm, I tried to re-create a similar scenario. I put multiple levels of dates in, and two dimensions in row. I created a calculation that counts records if they are not null.

With the added dimensions, I chose Compute Using > Pane (across).

It seems to work, the 3 months of each quarter always add up to 100%, and if there are multiple values in the quarter - it gets split into the correct percentages:

I understand why you can't share your data, but without seeing it I don't think I can troubleshoot any further .

Best,

Jennifer

• ###### 6. Re: How to create moving % of usage?

Hi Deepak,

I have created an example workbook with a sample of the underlying data with names changed. Let me know if you can think of anything as I cannot at all. Thanks!

Best Regards,

Ben

• ###### 7. Re: How to create moving % of usage?

Hi Jennifer,

I have created  sample workbook with underlying data but names change. Let me know if you can figure something out as I am helpless ha.

Best Regards,

Ben

• ###### 8. Re: How to create moving % of usage?

Hi Ben, oh I get it now. You explained this much earlier in the thread but I couldn't see it without your workbook:

The outcome of your Breadth formula will not only equal 1 for individual dimensions, it will equal 1 at a SubTotal and Total level as well. So it is always calculating Percent of Total as 1/1 for 100%.

I created a calculation to count the number of 1s in each Pane (Company/Transaction/Week) called Pane Total: WINDOW_COUNT([Breadth Count]). You can see it correctly counts the 1s in each Pane.

With that, we can now calculate the Percent of Total with: