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 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 

  • 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

     

7 Replies

    • Jit007's avatar
      Jit007
      Helper II

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

        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: