CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Chicken Sandwich No Pickles
Chicken Sandwich No Pickles

Reputation: 3362

DAX % of a Measure Above a Certain Threshold

I have a DAX measure, that works perfectly, called CntViewedByUser_YOY. If the user is exempt for some reason (asked to be exempt), the value takes N/A, otherwise, it will have a number.

I want to create a DAX measure that counts the total number of records where CntViewedByUser_YOY is not equal to N/A and use this as a denominator in a DIVIDE function later on.

For the numerator, I want the value to take a 1 if the value is equal to or higher than 70%, otherwise, 0, unless it's an N/A value, in those cases, I want the count to be ignored.

Example data:

CntViewedByUser_YOY
0.00
0.25
0.70
0.85
N/A

Numerator = 2 Denominator = 4

Result = 0.50

This is what I tried to use to get the denominator, but then I got an error message.

VAR Count_YOY_Adj = 
    IF ([CntViewedbyUser_YOY] <> "N/A" 
    , COUNT(HighlightsMeasures[CntViewedbyUser_YOY])
    ,BLANK()
    )

The error message I got is:

"Column 'CntViewedbyUser_YOY' in table 'table_a' cannot be found or may not be used in this expression.

Upvotes: 0

Views: 1248

Answers (1)

mkRabbani
mkRabbani

Reputation: 16908

Create this below measures-

Numerator = 
COUNTROWS(    
    FILTER(
        ALL('Table'),
        [CntViewedByUser_YOY] >= 0.70
    )
)

As you have 'N/A' as string value, you can filter liek - [CntViewedByUser_YOY] <> "N/A"

But, null is preferred in number type column.

Denominator = 
COUNTROWS(    
    FILTER(
        ALL('Table'),
        NOT ISBLANK([CntViewedByUser_YOY])
    )
)

Result = [Numerator]*1.00/[Denominator]

Final output-

enter image description here

Upvotes: 1

Related Questions