I have an aggregate calculated field - AvgAmountCalc = SUM(AvgAmount * No.of rows)/No.of rows
The reason I have to do this as a calculated field is because the value of this field will change based on the filter selections. And to avoid doing average of averages.
This calculation gives me the correct value for any given filter selections.
Then, for each row of the dataset, I need to use this value in a condition for another calculated field:
If SectionCharge-DiscountPrice>AvgAmountCalc Then 1 Else 0 End
basically i want to count the rows which satisfy the above condition.
But since AvgAmountClac is an aggregate filed, I cannot use it with the row level fields. Also, I cannot use SUM(SectionCharge)-Sum(DiscountPrice)>AvgAmountCalc because it's incorrect and I can't count the individual rows.
I know, passing the parameters to the source query is an option but as far as I know parameter values are not dynamic (if a new status comes through the data, the parameter will not pick it up automatically.) And I don't know whether cascading parameters are possible. (I use cascading filters now - not sure if filter values can be passed as parameters.
Is it possible to achieve what I'm after in Tableau? I'm using 9.1 version.
Thanks!
The answer depends on your data structure. It may be possible to use something like MIN(SectionCharge)-MIN(DiscountPrice)>AvgAmountCalc, or a Level of Detail (LoD) calculation. If you can attach a sample packaged workbook (.twbx), we might be able to find a solution. If you have confidential data, please see Anonymize your Tableau Package Data for Sharing |Tableau Support Community.