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
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 @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
In column few line results are just showing only %, One more thing below highlighted should have given result as SFRP/CURP i.e
(1/347= 0%)
Regards,
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.
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
