Removing -100% from a calculation

[Anonymous]

Hi all,

I have made a calculation where I want to see the how much the average amount of transport delays per customer correspond with the average amount of transport delays. I am the displaying this in a table where I have a customer on each row and the KPI on the next column. The logic of the calculation is built up like this:

1. Customer filtered in table: Calculate transport delay divided by deliveries (ex. 0.1, 0.14, 0.09)

2. Total: Calculate total transport delays divided by total amount of deliveries (ex. 0.2). 

3. Get the procentual difference form the average by taking step: (1-2)/2. Example, (0.1-0.2)/0.2 = -0.5 = -50%. 

This works well enough, but some customers don't have any transport delays and are thus -100% under the average. Is there any way to get these -100% out of the table so I don't see them? They don't add value so I'd rather have those fields in the table blank. Thank you in advance! 

[Anonymous · Accepted solution]

I solved it now. I created a second measurement that looked at my initial measurement and said "if the first measurement is -100%, then blank, otherwise show first measurement".

IF(measurement1=-1,"",measurement1)

[sg123]

I might be missing something, but can you just set up a flag to identify the -100% customers and then filter to exclude those?  Then do the calculations you need for the remaining customers?   Or set the -100% ones to 0, so they don't effect your calculation?

[Anonymous]

Hi! I want to keep the customers in the table (since I have more columns that are relevant for other things) so I don't want to filter out and exlude those.

The second proposal, set the -100% to 0 - can you elaborate on that one? Thanks in advance! 

EDIT: To clarify, the -100% (or any of the %) is not in a certain cell in a column, it's an aggretation of many different lines from the same customer since it's a distinctcount.