Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Team,
I had earlier posted this question, i want to calculated row level percentage and difference, challenge was few conditions output should be in String, but source columns are in number format. is there any way to get the below output.
DAX supported by @v-lid-msft lot.
Diffirent% =
SWITCH (
TRUE (),
//Condition No.1
ISBLANK ( [Unconstrained Requirement Plan (URP)] )
&& [Consolidated URP] > 0
&& [Submitted Final Requirement Plan] > 0, DIVIDE ( [Submitted Final Requirement Plan], [Consolidated URP] ),
//Condition No.2
ISBLANK ( [Consolidated URP] )
&& [Unconstrained Requirement Plan (URP)] > 0
&& [Submitted Final Requirement Plan] > 0, DIVIDE (
[Submitted Final Requirement Plan],
[Unconstrained Requirement Plan (URP)]
),
//Condition No.3
ISBLANK ( [Submitted Final Requirement Plan] )
&& [Consolidated URP] > 0, DIVIDE ( 1, [Consolidated URP] ),
//Condition No.4
ISBLANK ( [Consolidated URP] )
&& ISBLANK ( [Unconstrained Requirement Plan (URP)] )
&& [Submitted Final Requirement Plan] > 0, DIVIDE ( [Submitted Final Requirement Plan], 100 ),
//Condition No.19
[Consolidated URP] = 0
&& [Unconstrained Requirement Plan (URP)] = 0
&& [Submitted Final Requirement Plan] > 0, DIVIDE ( [Submitted Final Requirement Plan], 100 ),
//Condition No.6
ISBLANK ( [Submitted Final Requirement Plan] )
&& ISBLANK ( [Consolidated URP] )
&& [Unconstrained Requirement Plan (URP)] > 0, DIVIDE ( 1, [Unconstrained Requirement Plan (URP)] ),
//Condition No.7
ISBLANK ( [Unconstrained Requirement Plan (URP)] )
&& ISBLANK ( [Submitted Final Requirement Plan] )
&& ISBLANK ( [Consolidated URP] ), 0,
//Condition No.8 & Condition No.9
[Unconstrained Requirement Plan (URP)] = [Submitted Final Requirement Plan]
&& [Submitted Final Requirement Plan] = [Consolidated URP], 0,
//Condition No.10 & Condition No.12
[Consolidated URP] = 0
&& [Submitted Final Requirement Plan] > 0
&& [Unconstrained Requirement Plan (URP)] > 0, DIVIDE ( [Submitted Final Requirement Plan], 100 ),
//Condition No.11
[Unconstrained Requirement Plan (URP)] = 0
&& [Submitted Final Requirement Plan] > 0
&& [Consolidated URP] > 0, DIVIDE ( [Submitted Final Requirement Plan], [Consolidated URP] ),
//Condition No.13
ISBLANK ( [Unconstrained Requirement Plan (URP)] )
&& [Submitted Final Requirement Plan] > 0
&& [Consolidated URP] = 0, DIVIDE ( [Submitted Final Requirement Plan], 100 ),
//Condition No.14
ISBLANK ( [Consolidated URP] )
&& [Submitted Final Requirement Plan] > 0
&& [Unconstrained Requirement Plan (URP)] = 0, DIVIDE ( [Submitted Final Requirement Plan], 100 ),
//Condition No.15 & Condition No.16 & Condition No.17
ISBLANK ( [Submitted Final Requirement Plan] )
&& (
[Consolidated URP] = 0
|| ISBLANK ( [Consolidated URP] )
), 0,
//Condition No.18
[Submitted Final Requirement Plan] = 0, 0,
//Default Condition
DIVIDE (
[Submitted Final Requirement Plan],
[Consolidated URP],0))
Required a solution where i can bring percentage as will as string in same (Difference Column)
Thank you.
Regards,
Hari
Solved! Go to Solution.
Hi @harirao ,
To remove percentage decimal point, modify the formula like this:
For example, I have added two columns and modified the default condition formula:
//Default Condition
FORMAT(DIVIDE (
[Submitted Final Requirement Plan],
[Consolidated URP],
0
) * 100,"#") & "%"
About condition 8, not add format() function in it and the previous formula should work unless there is some repeated judgment logic in it, you should check your formula in details and try it again.
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @harirao ,
The reason that cause this issue is that the cardinality is too small, I have modified the default condition formula like this to avoid it:
//Default Condition
var _value =
DIVIDE (
[Submitted Final Requirement Plan],
[Consolidated URP],
0
) * 100
return
IF(
_value > 0 && _value < 1,
FORMAT(_value, "0.#") & "%", //"0%", // if you don't want to show 0% directly, just wrtie "0%" in this step
FORMAT(_value,"#") & "%"
)
As I commented in the formula, if you want to show 0% directly, just write "0%" and it will work.
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @harirao ,
When you create a calculated column, change the data type into text firstly, then I have modified your formula like this:
Column =
SWITCH (
TRUE (),
//Condition No.1
ISBLANK ( [Unconstrained Requirement Plan (URP)] )
&& [Consolidated URP] > 0
&& [Submitted Final Requirement Plan] > 0, DIVIDE ( [Submitted Final Requirement Plan], [Consolidated URP] ) * 100 & "%",
//Condition No.2
ISBLANK ( [Consolidated URP] )
&& [Unconstrained Requirement Plan (URP)] > 0
&& [Submitted Final Requirement Plan] > 0, DIVIDE (
[Submitted Final Requirement Plan],
[Unconstrained Requirement Plan (URP)]
) * 100 & "%",
//Condition No.3
ISBLANK ( [Submitted Final Requirement Plan] )
&& [Consolidated URP] > 0, "Blank SFRP",
//Condition No.4
ISBLANK ( [Consolidated URP] )
&& ISBLANK ( [Unconstrained Requirement Plan (URP)] )
&& [Submitted Final Requirement Plan] > 0, "0%",
//Condition No.19
[Consolidated URP] = 0
&& [Unconstrained Requirement Plan (URP)] = 0
&& [Submitted Final Requirement Plan] > 0, DIVIDE ( [Submitted Final Requirement Plan], 100 ) * 100 & "%",
//Condition No.6
ISBLANK ( [Submitted Final Requirement Plan] )
&& ISBLANK ( [Consolidated URP] )
&& [Unconstrained Requirement Plan (URP)] > 0, "Blank SFRP",
//Condition No.7
ISBLANK ( [Consolidated URP] )
&& ISBLANK ( [Submitted Final Requirement Plan] )
&& ISBLANK ( [Unconstrained Requirement Plan (URP)] ), "N/A",
//Condition No.8 & Condition No.9
[Unconstrained Requirement Plan (URP)] = [Submitted Final Requirement Plan]
&& [Submitted Final Requirement Plan] = [Consolidated URP]
&& NOT ( ISBLANK ( [Consolidated URP] ) )
&& NOT ( ISBLANK ( [Submitted Final Requirement Plan] ) )
&& NOT ( ISBLANK ( [Unconstrained Requirement Plan (URP)] ) ), "0%",
//Condition No.10
[Consolidated URP] = 0
&& [Submitted Final Requirement Plan] > 0
&& [Unconstrained Requirement Plan (URP)] > 0, "0%",
//Conditon No.11
[Unconstrained Requirement Plan (URP)] = 0
&& [Submitted Final Requirement Plan] > 0
&& [Consolidated URP] > 0, DIVIDE ( [Submitted Final Requirement Plan], [Consolidated URP] ) * 100 & "%",
//Condition No.13
ISBLANK ( [Submitted Final Requirement Plan] )
&& [Unconstrained Requirement Plan (URP)] > 0
&& [Consolidated URP] = 0, "Blank SFRP",
//Condition No.14
ISBLANK ( [Unconstrained Requirement Plan (URP)] )
&& ISBLANK ( [Submitted Final Requirement Plan] )
&& [Consolidated URP] = 0, "Blank SFRP",
//Condition No.15
[Unconstrained Requirement Plan (URP)] = 0
&& ISBLANK ( [Consolidated URP] )
&& ISBLANK ( [Submitted Final Requirement Plan] ), "Blank SFRP",
//Condition No.16
[Submitted Final Requirement Plan] = 0
&& ISBLANK ( [Consolidated URP] )
&& ISBLANK ( [Unconstrained Requirement Plan (URP)] ), "0%",
//Default Condition
DIVIDE (
[Submitted Final Requirement Plan],
[Consolidated URP],
0
) * 100 & "%"
)
Sample file is attached, please check and try it: Row level Percentage & Difference calculation for each line wise.pbix
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-yingjl,
Thanks for providing the solution it is working almost fine, for two things need your assistance,
a) removing the decimals for percentage & b) regarding condition 8 is not giving correct results it should be 0%
Thank you
Regards,
Hari
Hi @harirao .
To remove the decimals you can adapt the format.
Format(table[column],"0%")
Where you see the "0%" is to put the format without any decimals. if you use "0.0%" you will see one decimal, etc.
Related to the condition 8, you have in the measure that the condition 8 and 9 are the same so, maybe you need to analyze some exmaples and correct the formula or create condition 8 and 9 separately
Hi @dobregon, @v-yingjl
Thanks for your response, i tried with using Format function but unable to get result, also i tried creating new column still same issue.
Created new column.
Related to the condition 8, i have created column not measure, so how can i bring this conditions inside dax?
Thank you.
Regards,
Hari
Hi @harirao ,
To remove percentage decimal point, modify the formula like this:
For example, I have added two columns and modified the default condition formula:
//Default Condition
FORMAT(DIVIDE (
[Submitted Final Requirement Plan],
[Consolidated URP],
0
) * 100,"#") & "%"
About condition 8, not add format() function in it and the previous formula should work unless there is some repeated judgment logic in it, you should check your formula in details and try it again.
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-yingjl ,
Thanks for your reply format function is working fine also tuned condition 8&9.
I have included another condition, when ever SFRP is 0 and URP & CURP is greater than 0
//Condition No.18
Hi @v-yingjl @dobregon ,
Based on the calculated column i have create another Column, as Threshold
Hi @harirao ,
Since the first calculated column is a text column, you cannot compare it with number value directly. You can create the threadhold column which refers this formula and add your conditions in the {} to judge(it should take a long time in your report):
Threadhold =
IF('Table'[Column] in {"90%","120%"},"100%-150%",BLANK())
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-yingjl ,
Thanks for your response, multiply condition cannot included in Threshold.
I am facing one issue in the DAX for Row level Percentage & Difference calculation for few line only. result is %
Default condition is SFRP/CURP (1/347= 0%)
DAX:
Can you please help me on this.
Thank you
Regards,
Hari
Hi @harirao ,
The reason that cause this issue is that the cardinality is too small, I have modified the default condition formula like this to avoid it:
//Default Condition
var _value =
DIVIDE (
[Submitted Final Requirement Plan],
[Consolidated URP],
0
) * 100
return
IF(
_value > 0 && _value < 1,
FORMAT(_value, "0.#") & "%", //"0%", // if you don't want to show 0% directly, just wrtie "0%" in this step
FORMAT(_value,"#") & "%"
)
As I commented in the formula, if you want to show 0% directly, just write "0%" and it will work.
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
HI @harirao ,
You will need to use format function
When a % is expected use FORMAT('Table'[Value], "percent")
e.g
Hi @harshnathani,
Can you please help me to implement same for the below DAX got confused.
Thank you
regards,
Hari
Diffirent%_Condition=
SWITCH (
TRUE (),
//Condition No.1
ISBLANK ( [Unconstrained Requirement Plan (URP)] )
&& [Consolidated URP] > 0
&& [Submitted Final Requirement Plan] > 0, DIVIDE ( [Submitted Final Requirement Plan], [Consolidated URP] ),
//Condition No.2
ISBLANK ( [Consolidated URP] )
&& [Unconstrained Requirement Plan (URP)] > 0
&& [Submitted Final Requirement Plan] > 0, DIVIDE (
[Submitted Final Requirement Plan],
[Unconstrained Requirement Plan (URP)]
),
//Condition No.3
ISBLANK ( [Submitted Final Requirement Plan] )
&& [Consolidated URP] > 0, DIVIDE ( 1, [Consolidated URP] ),
//Condition No.4
ISBLANK ( [Consolidated URP] )
&& ISBLANK ( [Unconstrained Requirement Plan (URP)] )
&& [Submitted Final Requirement Plan] > 0, DIVIDE ( [Submitted Final Requirement Plan], 100 ),
//Condition No.19
[Consolidated URP] = 0
&& [Unconstrained Requirement Plan (URP)] = 0
&& [Submitted Final Requirement Plan] > 0, DIVIDE ( [Submitted Final Requirement Plan], 100 ),
//Condition No.6
ISBLANK ( [Submitted Final Requirement Plan] )
&& ISBLANK ( [Consolidated URP] )
&& [Unconstrained Requirement Plan (URP)] > 0, DIVIDE ( 1, [Unconstrained Requirement Plan (URP)] ),
//Condition No.7
ISBLANK ( [Unconstrained Requirement Plan (URP)] )
&& ISBLANK ( [Submitted Final Requirement Plan] )
&& ISBLANK ( [Consolidated URP] ), 0,
//Condition No.8 & Condition No.9
[Unconstrained Requirement Plan (URP)] = [Submitted Final Requirement Plan]
&& [Submitted Final Requirement Plan] = [Consolidated URP], 0,
//Condition No.10 & Condition No.12
[Consolidated URP] = 0
&& [Submitted Final Requirement Plan] > 0
&& [Unconstrained Requirement Plan (URP)] > 0, DIVIDE ( [Submitted Final Requirement Plan], 100 ),
//Condition No.11
[Unconstrained Requirement Plan (URP)] = 0
&& [Submitted Final Requirement Plan] > 0
&& [Consolidated URP] > 0, DIVIDE ( [Submitted Final Requirement Plan], [Consolidated URP] ),
//Condition No.13
ISBLANK ( [Unconstrained Requirement Plan (URP)] )
&& [Submitted Final Requirement Plan] > 0
&& [Consolidated URP] = 0, DIVIDE ( [Submitted Final Requirement Plan], 100 ),
//Condition No.14
ISBLANK ( [Consolidated URP] )
&& [Submitted Final Requirement Plan] > 0
&& [Unconstrained Requirement Plan (URP)] = 0, DIVIDE ( [Submitted Final Requirement Plan], 100 ),
//Condition No.15 & Condition No.16 & Condition No.17
ISBLANK ( [Submitted Final Requirement Plan] )
&& (
[Consolidated URP] = 0
|| ISBLANK ( [Consolidated URP] )
), 0,
//Condition No.18
[Submitted Final Requirement Plan] = 0, 0,
//Default Condition
DIVIDE (
[Submitted Final Requirement Plan],
[Consolidated URP],
0))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |