Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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
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 😞
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:
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
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
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
40 | |
30 | |
26 | |
19 | |
17 |