Forum Discussion
Jit007
2 years agoHelper II
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...
- 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
lbendlin
2 years agoSuper User
- Jit0072 years agoHelper 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%
lbendlin earlier this test file was not attached 😞
- dufoq32 years agoCommunity 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: