Divide by applicable values

[Jit007]

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 applicable for that Q

Now I need to show No / Total applicable means 10/35 = 29%

How to achieve this in power bi.. thanks @lbendlin 

[lbendlin]

[Jit007]

Thanks for your response..but i'm looking for option to show this result in matrix visual in single line - monthly trend (total met Yes) - if i add month in column this is not showing correct value 

                                          month 1   month2  month3

Q1A.Paramater Checked - xx.xx%     xx.xx%     xx.xx%

Test Q PBI.pbix

@lbendlin  earlier this test file was not attached 😞 

[dufoq3]

Hi @Jit007, like this? You just need to sort Month column via month number.

If you don't know how to create dynamic month number based on your data, add this as custom column in power query:

Date.Month(Date.FromText("2024-" & Text.Proper([Month]) & "-01", [Format="yyyy-MMM-dd", Culture="en-US"]))

Then in Power Bi table sort by this column:

 Result:

[Jit007]

@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 · Accepted solution]

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]

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]

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