How to create a dimension to filter out of measure?

[brickanalyst]

Hello there,

I actually have two questions: 

First, I have a calculated column which is basically a variance calc of two columns from two different table. And it's decimal with 2 places. This is the column;  I keep them as percentages it works fine. 

However I realize I need to use measure so I have measure and it works perfectly fine. When I put these two values together,

each value is assigned on calculated column is the same in measure (measure on the left, cal. col o the right) 

when I use card visual (on the right) measure is = -8.04% and cal. col. is coming as count and i change it to average.
Why are these two values different? Table view brings a total for this measure, how it's being calculated ? It's not sum, min, max so what is it ?

Second,

Is it possible that I use variance measure to create another measure to filter if my projects are outlier or not?
Let's say it's out of +/- 20.00% is outliers. Can we use that measure to use in a filter to select : outliers, non-outliers?

if it's NOT possible, let's say we have to use calculated column, so how can't this dax work ? 
IsOutlier =
VAR _convertedValue = CONVERT('Table'[Variance], DOUBLE)
VAR _checkOutlier = IF( ABS(_convertedValue) > 20.00, "Outlier", "Non-Outlier")

RETURN _checkOutlier

[Anonymous · Accepted solution]

Hi @brickanalyst ,

The different values returned by measure are affected by the DAX you are using. Since I don't know what DAX you are using, I can't give you a detailed explanation in your scenario, so please provide me with the DAX that corresponds to your measure and the sample data involved.

But I can give you a simple example, here is my sample data:

I want to calculate the Value1 column divided by the Value2 column, and I have created a MEASURED and CALCULATED columns to do the calculation respectively:

test measure = SUM('Table'[Value1]) / SUM('Table (2)'[Value2])

test column = 'Table'[Value1] / RELATED('Table (2)'[Value2])

They both display the same values in the columns of the table visual object, but the Total and the data displayed in the card are not the same. 
That's because the calculation performed by the calculated column is simply adding up the results:

0.83+0.95+0.91=2.69

But measure performs the calculation of:

(1+2+3)/(1.2+2.1+3.3)=0.91

He does the calculation based on the DAX corresponding to the measure.
If you want this measure to return the same value as the calculated column, you can try adding a new measure:

Measure = SUMX(VALUES('Table'[Index]), [test measure])

I hope the above explanation will help you!

In the pbix I provided I tried to write my own DAX to calculate the mean of the variance, you may refer to it:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[brickanalyst]

@Anonymous  
And how about this ? 

Is it possible that I use variance measure to create another measure to filter if my projects are outlier or not?
Let's say it's out of +/- 20.00% is outliers. Can we use that measure to use in a filter to select : outliers, non-outliers?

if it's NOT possible, let's say we have to use calculated column, so how can't this dax work ? 
IsOutlier =
VAR _convertedValue = CONVERT('Table'[Variance], DOUBLE)
VAR _checkOutlier = IF( ABS(_convertedValue) > 20.00, "Outlier", "Non-Outlier")

RETURN _checkOutlier

[Anonymous]

Hi @brickanalyst ,

Sorry I forgot about this question😂
This is the output of the variance measure:

And I create these two measures:

Flag = 
IF(
    [variance Measure] > 0.003,
    "Outlier",
    "Non-Outlier"
)

IsOutlier = MAXX(VALUES('Table'[Value1]), 'Table'[Flag])

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[brickanalyst]

I'm sorry I already get my measure worked by fixing 20.00 to 0.20 because i forgot it was decimal.
My question was here, we can't use measure to filter the data right? meaning we have to have a dimension (calculated column) to either show-case outlier or non-outlier than we can use that column as a filter? 

ps: i'm not talkin about using measure to filter this page. we both know you can't control that and user wants to click to choose whether outliers or non-outliers.

[brickanalyst]

Scenario is the exact same scenario what I have, when I get column and measure next to each other I'm seeing same values for each of them. Just like you shared the screenshot 0.83 | 0.83 etc.

I think understand, when I pull calculated column in a card visual. Automatically it brings count and i switched to average, and when I get the measure and use another measure to get the result with
AVERAGEX function i get the same result. 

I guess once you look at your results you need to know what you want to see as a total as well.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[brickanalyst]

I won't be able to share the link it contains sensitive information.

[brickanalyst]

Second question: 
My bad it should be 0.20 instead of 20.00