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

     

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

     

    • 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