The issue with avg can be avoided if you change your zeros to NULLS.
You may do it - either in the data or in the tableau.
From data, you can remove the contents from the measurements where they are currently marked as zero.
Inside tableau, since if else statements doesn't allow the use of NULL in their results, we can use this small trick.
Calculate the inverse of the field you are using are values that are to be aggregated.
This removes zeros and makes them null and assumes the same on further calculations
This reverses your numbers back to normal and replaces zeros with Nulls.you can now use this field for any aggregations.
Hi, thanks for your answer.
My data is already cleaned of any zero values. When I look at the data in Tableau, it reads the missing values as "null"
I'm not sure if you had a look at the script I included, but it doesn't have any "avg" calculations.
When I use the calculation for 10 measurements, this is what the graph looks like from before and after the 10 measurements were introduced:
It would help to understand more about your data itself. Are you using a data source for which you have the option to pivot the separate measure columns to create two columns: effectively Metric Description and Metric Value? Then you can simply take max([metric value]) - min([metric value]) for given dimensions.
Can you attach a .twbx of your data, or similar mocked-up data if your data is confidential? This will help folks in the community give relevant suggestions.
For instance, if your data is something like this:
And if you are using Excel as data source, you can pivot it in Tableau to make it like this:
And then you can easily calculate all kinds of descriptive stats on your metrics, including % dif.
Hi, thank you!
I'm not sure if I can use pivot tables, but I'll look into it.
Here's an example of my data. As you can see, the measures apply to specific "Setup numbers".
I want to find the % difference between the highest and lowest measurements, for each individual Setup Number. The value for each Setup Number becomes one dot in the scatter plot.
The problem comes when there are no values for Measure 9 & 10.
Date Setup no. Measure1 Measure2 Measure3 Measure4 Measure5 Measure6 Measure7 Measure8 Measure9 Measure10 01.01.2016 34522 17 12 15 13 11 12 14 12 05.03.2016 34753 9 15 9 8 9 15 9 15 08.05.2016 23567 16 13 17 16 16 13 16 13 11.07.2016 53432 8 9 8 14 8 9 8 9 8 14 13.09.2016 53256 14 13 14 9 14 13 14 13 14 9 16.11.2016 44678 9 14 13 15 9 14 9 14 13 15
I see. I think pivoting you measures is your best bet - if you have a data source that can be pivoted. To check, in Tableau's Data Pane, select the ten measures, click the little arrow, and see if you have the option to Pivot.
If so, then your data looks like this:
I think the data will be easier to work with in every respect. You can create your % DIF calculation like this.
And your scatter plot (not sure what the x-axis should be):
Hope this helps ,
If that isn't an option, you can handle the nulls in your calculation as you have it now with an IF statement as you originally suggested. Here is your current calculation with measures 9 and 10 added to the logic. I can more clearly see your issue here, that nulls in M9 and M10 cause a null result for % Dif.
And with an IF statement... It's just so very specific to handling only if nulls are found in measures 9 and 10, it won't handle any other null situation that might arise.
This assumes that if Measure9 is null, measure10 is also null.
Workbook is attached.
Nexted max and min.twbx 39.1 KB
Thank you Jennifer!
The solution with the IF statement worked perfectly!