Forum Discussion

Jit007's avatar
Jit007
Helper II
2 years ago
Solved

Divide by applicable values

I'm new to PBI & need help in below topic.   I have data as below   Q1 NA 100. Record  No.  10 Record Yes. 25. Record Total - 135 checked records   Yes  = 10 score & No = 00. NA-Not applica...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Check this. You can replace _parameterNotNA by _parameter if you want to see also NA vs YesNo in _current part of code.

     

     

    Applicable dufoq3 = 
VAR _parameter = SELECTEDVALUE('Table Test'[Q1A Parameter Checked])
VAR _parameterNotNA = IF(_parameter <> "NA", _parameter)

VAR _current = 
    CALCULATE(
        //SUM('Table Test'[Q1 score]),
        COUNTROWS('Table Test'),
        KEEPFILTERS('Table Test'[Q1A Parameter Checked] = _parameterNotNA)
    )

VAR _yesNo = 
    CALCULATE(
        //SUM('Table Test'[Q1 score]),
        COUNTROWS('Table Test'),
        'Table Test'[Q1A Parameter Checked] IN {"yes", "no"}
    )

VAR _result = DIVIDE(_current, _yesNo)

RETURN _result

     

Jit007's avatar
Jit007
Helper II
2 years ago

Thanks dufoq3 i got some ideas from above code & replaced with my requirements..Appreciated your help.

 

I'm trying to find how i can replace empty cell with some Text in divide formula

 

,0)+0 = 0% == dont want to show 0% instead of that i can use some NA, text 

dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi, you can use 3rd argumend of DIVIDE function, or create new variable in same code:

 

VAR _result2 = IF(_result = 0, "NA", _result)

and don't forget to replace:
RETURN _result
by
RETURN _result2