Forum Discussion
Solved
DistinctCount If Greater Than A Measure
I am trying to figure out the number of orders which would be an outlier.
To this end, I created a measure to calculate the cutoff for an outlier.
Upper Outlier Limit =
[Quartile 3] + ( ( [Quartile 3] - [Quartile 1] ) * 1.5 )
I also created a measure for a basic distinct count of orders:
# of Orders =
DISTINCTCOUNT(Orders_Merged_Final[Sales Order ID])
To determine how many orders would qualify as an outlier, I made a test formula with a static number as the outlier cutoff:
# of Outlier Orders =
CALCULATE (
[# of Orders],
FILTER ( Orders_Merged_Final, Orders_Merged_Final[Total Order Value] > 50000 )
)
That formula works great, however, when I replace the static outlier cutoff with the measure that calculates the outlier I just get blanks:
# of Outlier Orders =
CALCULATE (
[# of Orders],
FILTER ( Orders_Merged_Final, Orders_Merged_Final[Total Order Value] > 'Measure'[Upper Outlier Limit] )
)
I will be using these results with filters on Geo and type of customers, and so forth, and depending on the filters applied in the visual, what is considered an outlier will change.
Any ideas on how I can fix my formula so it uses the measure correctly?
Any ideas on how I can fix my formula so it uses the measure correctly?
Figured it out. As most people probably know you cannot use the result of a measure as a filter (I am a slow learner), however, if you turn your measure into a variable, you can call up the variable. This worked for me:
# of Outlier Orders =VAR Upper_Outlier = 'Measure'[Upper Outlier Limit]RETURNCALCULATE ([# of Orders],Orders_Merged_Final[Total Order Value] > Upper_Outlier)
1 Reply
Figured it out. As most people probably know you cannot use the result of a measure as a filter (I am a slow learner), however, if you turn your measure into a variable, you can call up the variable. This worked for me:
# of Outlier Orders =VAR Upper_Outlier = 'Measure'[Upper Outlier Limit]RETURNCALCULATE ([# of Orders],Orders_Merged_Final[Total Order Value] > Upper_Outlier)