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

dufoq3 lbendlin this doesn't give correct value if i add another month in matrix (total not matching 100%) -- also i want to present data in single line as i have 10 more paramter Q to show it in matrix visual -- 

 

example 

 

 

 

dufoq3's avatar
dufoq3
Community Champion
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