Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I need to create a measure that calculates a percentage but I need to use an if statement. I have calculated the count of each field but I'm not sure how to add the if statement. Below is what I have so far.
=CALCULATE(COUNTA([myvalue]))/CALCULATE(COUNTA([ORGACCOUNTNUMBER]))
The if would apply to the [myvalue] that has a value of Completed.
I am new to DAX and not sure how to do this.
Solved! Go to Solution.
@Anonymous
Try this
Divide(
CALCULATE (COUNTA (WP_plan[MyColumn]), WP_plan[MyColumn] = "Completed"),
CALCULATE(COUNTA(WP_plan[ORGACCOUNTNUMBER])),
0)
Try this : If ORGACCOUNTNUMBER is Interger value
Divide(
CALCULATE (COUNTA (WP_plan[MyColumn]), WP_plan[MyColumn] = "Completed"),
CALCULATE(COUNT(WP_plan[ORGACCOUNTNUMBER])),
0)
DIVIDE ( CALCULATE ( COUNTA ( [myvalue] , [myvalue = "Completed"] ) ), COUNTA ( [myvalue] ) )
In this case, you really don't need an IF statement, but rather to filter your data on your condition.
Doesnt quite get me there. Here is what I tried.
=Divide (
CALCULATE(COUNTA ([myvalue], [myvalue = "Completed"])) / CALCULATE(COUNTA([ORGACCOUNTNUMBER]))
It just gives an error that it's incomplete.
This:
=Divide ( CALCULATE(COUNTA ([myvalue], [myvalue = "Completed"])) / CALCULATE(COUNTA([ORGACCOUNTNUMBER]))
is missing the closing parenthesis.
@Anonymous wrote:Doesnt quite get me there. Here is what I tried.
=Divide (
CALCULATE(COUNTA ([myvalue], [myvalue = "Completed"])) / CALCULATE(COUNTA([ORGACCOUNTNUMBER]))
It just gives an error that it's incomplete.
@Anonymous
What is [myvalue] here ? it is measure or column ?
Better can you please share the sample table.
It will help us to close this issue asap.
I have tried to use this as well
=CALCULATE (COUNTA (WP_plan[MyColumn], [MyColumn = "Completed"])) / CALCULATE(COUNTA(WP_plan[ORGACCOUNTNUMBER]))
The error says the maximum arguement count for the function is 1.
MyColumn is a column in the table. The values could be Outstanding or Complete. I need to do this calculation on Completed.
You have the parenthesis in the wrong place.
=CALCULATE (COUNTA (WP_plan[MyColumn] ) , [MyColumn = "Completed"]) / CALCULATE(COUNTA(WP_plan[ORGACCOUNTNUMBER]))
I'd also recommend this free Intro to Dax online video:
https://www.sqlbi.com/training/dax/
And this DAX documentation, which is a little friendlier than the official:
https://dax.guide/
Best of luck!
Everytime I try this I get the following error.
Calculation error in measure 'WP_plan'[CircleCaringComp]: The value for 'CircleCaring = "Completed"' cannot be determined. Either 'CircleCaring = "Completed"' doesn't exist, or there is no current row for a column named 'CircleCaring = "Completed"'.
I do know the column exist and there are rows in that column where the value is "Completed".
Going through online documentation it seems this should work. Not sure what the issue is.
@Anonymous
Try this
Divide(
CALCULATE (COUNTA (WP_plan[MyColumn]), WP_plan[MyColumn] = "Completed"),
CALCULATE(COUNTA(WP_plan[ORGACCOUNTNUMBER])),
0)
Try this : If ORGACCOUNTNUMBER is Interger value
Divide(
CALCULATE (COUNTA (WP_plan[MyColumn]), WP_plan[MyColumn] = "Completed"),
CALCULATE(COUNT(WP_plan[ORGACCOUNTNUMBER])),
0)
myvalue is a column. The value could be outstanding or completed. I need to do this calculation based on a completed task
Doesnt quite get me there. Here is what I tried.
=Divide (
CALCULATE(COUNTA ([myvalue], [myvalue = "Completed"])) / CALCULATE(COUNTA([ORGACCOUNTNUMBER]))
It just gives an error that it's incomplete.
@Anonymous please spend some time on this blogs it will help to move further in DAX.
User | Count |
---|---|
124 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |