6 Replies Latest reply on Dec 5, 2018 1:36 AM by marian.eerens

# Calculating accuracy (%) using the count of multiple dimensions

For a work project I need to re-create the following calculation but so far have been unable to make it work. I admit not enough knowledge of more complex calculations but working on that. In the meantime any help with getting the below to work would be much appreciated.

Accuracy% =  (Number of Orders - Number of Returns with Code 5) / Number of Orders

I created a sample workbook with a mock-up the data I'm working with (see attached).

- Different types of transactions in the same table

- Transactions have multiple lines

- Any of the transaction may or may not have a specific code

If any additional clarification is needed, let me know, first time posting asking for help so hope I gave sufficient information.

Sidenote: we're currently using 10:5

• ###### 1. Re: Calculating accuracy (%) using the count of multiple dimensions

Hi Marian,

Here's the calculation you're looking for:

(COUNTD([Order Id]) - COUNTD(if [Type] = "Return" and [Reason] = "5" then [Order Id] end))

/

COUNTD([Order Id])

I've updated the attached workbook with this.  Let me know if you have any questions.

Best,

Paul

1 of 1 people found this helpful
• ###### 2. Re: Calculating accuracy (%) using the count of multiple dimensions

Good afternoon

I broke the calcs into pieces but they could all be combined

first the order count (by day (I assumed you would want that)

returns type 5

and the Accuracy %

it returns this by day

or this in summary

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 3. Re: Calculating accuracy (%) using the count of multiple dimensions

Jim Dehner Paul Wachtler Thanks for the input!!

Looks like both approaches are similar - didn't know you could nest an IF statement in COUNTD so that really solved it. I tested it with the data-source from work and the numbers add up,

For some reason the formula in the sampler from Paul (which I had the tweak a bit) didn't add up and can't see why - would you mind taking another look because I would like to understand what I'm missing.

This is what I made of it

(COUNTD (if [Type]="Order" then[Order Id] END) - COUNTD(if [Type] = "Return" and [Reason] = "5" then [Order Id] end))

/

COUNTD([Order Id])

I have to be able to add the Date Dimension and if I do the calculations manually: I would expect the % of January to show as (11-3)/11 = 72,72% yet according to attached workbook it says 40%.

Attached the solution from work, after pulling some samples the numbers add up there. The data I'm working with in real life is a bit more complex, wish the calculations could be simpler (perhaps I' missing something) but it works.

• ###### 4. Re: Calculating accuracy (%) using the count of multiple dimensions

Hi Marian,

Apologies, realized my mistake.  I didn't filter the bottom for the "Order" type so it's using all of the order numbers in the denominator, regardless of type.  Try this instead:

(COUNTD (if [Type]="Order" then[Order Id] END) - COUNTD(if [Type] = "Return" and [Reason] = "5" then [Order Id] end))

/

(COUNTD (if [Type]="Order" then[Order Id] END)

Best,

Paul

• ###### 5. Re: Calculating accuracy (%) using the count of multiple dimensions

Hi Paul - that part I had already updated but it doesn't add up and wondering what I missed. The percentage for Jan should be 72%

11 Orders

3 Credits with Reason Code 5

(11-3) / 11 = 72,72%

Even if I remove the filter (set to Orders and Returns for the cap) below ... it gives me 40%

• ###### 6. Re: Calculating accuracy (%) using the count of multiple dimensions

Closing this because the I was able to leverage the info and create a solution for work (details in attachment).

The help has helped