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 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
    2 years ago

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