Divide function is showing blank results

[MurVeman]

Hello all,

I am deviding the records from a colum "TestDefID" by counting the records with various statuses (Defect Correction in Process", "In Process", "Information Required", "Solution Proposal", "Forwarded", "Tester Action", "Wait for Defect Correction", "Confirmed", "Build Block", "Withdrawn","New") with records from the same table coum "UniqueTestCaseDescription" by counting deferent statuses("Pass", "InProgress", "Fail"). Here is the formula that i am using. Please take a look and advise.

(Formula = Total number of records with specific status/Total number of records with specific status*100)

Here is the Dax formula : - 

DefectDensity =
CALCULATE(
DIVIDE(
CALCULATE(
DISTINCTCOUNT(VR_DefectsManster[TestDefID]),
VR_DefectsManster[TestDefStatusText] IN {"Defect Correction in Process", "In Process", "Information Required", "Solution Proposal", "Forwarded", "Tester Action", "Wait for Defect Correction", "Confirmed", "Build Block", "Withdrawn","New"}
),
CALCULATE(
DISTINCTCOUNT(VR_DefectsManster[Unique]),
VR_DefectsManster[DefectsStatsGrouping] IN {"Pass", "InProgress", "Fail"}
)))

Please let me know if you need any aditional details for better ubderstanding. 

[MurVeman · Accepted solution]

@Anonymous 

Resolved, formula was correct, there was a problem with relationships. 

[rajendraongole1]

Hi @MurVeman -  create a measure to calculate the Defect Density and to ensure it correctly filters.

DefectDensity =
VAR DefectCount =
CALCULATE(
DISTINCTCOUNT(VR_DefectsManster[TestDefID]),
VR_DefectsManster[TestDefStatusText] IN {
"Defect Correction in Process", "In Process", "Information Required",
"Solution Proposal", "Forwarded", "Tester Action",
"Wait for Defect Correction", "Confirmed", "Build Block",
"Withdrawn", "New"
}
)

VAR TestCaseCount =
CALCULATE(
DISTINCTCOUNT(VR_DefectsManster[Unique]),
VR_DefectsManster[DefectsStatsGrouping] IN {
"Pass", "InProgress", "Fail"
}
)

VAR Result =
IF(
TestCaseCount > 0,
DIVIDE(DefectCount, TestCaseCount, 0) * 100,
BLANK()
)

RETURN
Result

Once the above measure created, you can add percentage by enabling the % icon in measure tools by selecting the created measure

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[MurVeman]

Hi Rajendra,

Thank you for taking time out for helping me. I have tried with the solution, it is still showing blank. Please see the scheenshot below. 

[Anonymous]

HI @MurVeman,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[MurVeman]

Hi @Anonymous,

Thanks for your reply.

I have updated the approach to calculate the defect density now, but still no luck. Here is the new formula and sample data.

DefectDensity =
CALCULATE(
DIVIDE(
CALCULATE(
DISTINCTCOUNT(VR_DefectsManster[TestDefID]),
VR_DefectsManster[TestDefStatusText] IN {"Defect Correction in Process", "In Process", "Information Required", "Solution Proposal", "Forwarded", "Tester Action", "Wait for Defect Correction", "Confirmed", "Build Blocked", "New"}
),
CALCULATE(
COUNT('VR_PlanTesterTable'[Merged-UniqueID]),
VR_PlanTesterTable[StatusGrouping]IN {"Pass", "InProgress", "Fail"}

)))

Table - VR_PlanTesterTable

Merged-UniqueID	TCaseOverallStatusText	StatusGrouping	TPlanID
GPS - PPE;MMPR-Min maint;S-PPE-003-001#PR min check	OK	Pass	GPS - PPE
GPS - PPE;MMPR-Min maint;S-PPE-003-002#PR min check	OK	Pass	GPS - PPE
GPS - PPE;MM PR min check - IPPF;S-PPE-006-001#PR min check - IPPF	OK	Pass	GPS - PPE
GPS - PPE;Sales order creation;S-PPE-001-026#SO - BULK - M1 Italy	OK	InProgress	GPS - PPE
GPS - PPE;Sales order creation;S-PPE-001-025#SO - PROMO - M2 Fashion	OK	InProgress	GPS - PPE
GPS - PPE;Sales order creation;S-PPE-001-024#SO - BULK - M2 Fashion	OK	InProgress	GPS - PPE
GPS - PPE;Sales order creation;S-PPE-001-023#SO - BULK - M2 Australia	OK	Fail	GPS - PPE
GPS - PPE;Sales order creation;S-PPE-001-022#SO - BULK - M2 Canada	OK	Fail	GPS - PPE
GPS - PPE;Sales order creation;S-PPE-001-021#SO - BULK - M1 UK	OK	Fail	GPS - PPE
GPS - PPE;Sales order creation;S-PPE-001-020#SO -PROMO- M1 UK	OK	Fail	GPS - PPE
GPS - PPE;Sales order creation;S-PPE-001-006#SO - PROMO - M1.5 China	OK	Fail	GPS - PPE
GPS - PPE;Sales order creation;S-PPE-001-005#SO - PROMO - M1.5 South Korea	OK	Fail	GPS - PPE
GPS - PPE;Sales order creation;S-PPE-001-027#SO - BULK - M1 Spain	OK	Pass	GPS - PPE
GPS - PPE;Sales order creation;S-PPE-001-028#SO - BULK - M2 Israel	OK	Pass	GPS - PPE
GPS - PPE;Sales order creation;S-PPE-001-001#SO - PROMO - M1 Japan	OK	Pass	GPS - PPE
GPS - PPE;Sales order creation;S-PPE-001-002#SO - PROMO - M1 Spain	OK	Pass	GPS - PPE
GPS - PPE;Sales order creation;S-PPE-001-003#SO - BULK - M1 Japan	OK	Pass	GPS - PPE
GPS - PPE;Sales order creation;S-PPE-001-002#SO - PROMO - M1 Spain1	OK	Pass	GPS - PPE
GPS - PPE;Sales order creation;S-PPE-001-003#SO - BULK - M1 Japan2	OK	Pass	GPS - PPE

Table 2 - VR_DefectsManster

TestDefID	TestDefStatusText	Merged-UniqueID	DefectsStatsGrouping	TPlanID
8000009121	Defect Correction in Process	GPS - PPE;MMPR-Min maint;S-PPE-003-001#PR min check	Closed	TP_ACCESS_MGMT
8000009234	Defect Correction in Process	GPS - PPE;MMPR-Min maint;S-PPE-003-002#PR min check	Closed	TP_S4_TRAINING
8000009243	New	GPS - PPE;MM PR min check - IPPF;S-PPE-006-001#PR min check - IPPF	Closed	TP_S4_TEMPLATE_BUILD
8000009281	In Process	GPS - PPE;Sales order creation;S-PPE-001-026#SO - BULK - M1 Italy	Closed	TP_S4_W1SP3_SOURCE TO PAY
8000009287	In Process	GPS - PPE;Sales order creation;S-PPE-001-025#SO - PROMO - M2 Fashion	Closed	TP_S4_W1SP3_ORDER TO CASH
8000009293	Information Required	GPS - PPE;Sales order creation;S-PPE-001-024#SO - BULK - M2 Fashion	Closed	TP_S4_W1SP3_ORDER TO CASH
8000009294	Information Required	GPS - PPE;Sales order creation;S-PPE-001-023#SO - BULK - M2 Australia	Closed	TP_S4_W1SP3_ORDER TO CASH
8000009297	Build Blocked	GPS - PPE;Sales order creation;S-PPE-001-022#SO - BULK - M2 Canada	Closed	TP_S4_W1SP3_ORDER TO CASH
8000009298	Solution Proposal	GPS - PPE;Sales order creation;S-PPE-001-021#SO - BULK - M1 UK	Closed	TP_S4_W1SP3_TREASURY
8000009299	Solution Proposal	GPS - PPE;Sales order creation;S-PPE-001-020#SO -PROMO- M1 UK	Closed	TP_S4_W1SP3_TREASURY
8000009300	Forwarded	GPS - PPE;Sales order creation;S-PPE-001-006#SO - PROMO - M1.5 China	Closed	TP_S4_W1SP3_ORDER TO CASH
8000009308	Forwarded	GPS - PPE;Sales order creation;S-PPE-001-005#SO - PROMO - M1.5 South Korea	Closed	TP_S4_W1SP3_ORDER TO CASH
8000009311	Tester Action	GPS - PPE;Sales order creation;S-PPE-001-027#SO - BULK - M1 Spain	Closed	TP_S4_W1SP3_PLAN TO REALIGN
8000009312	Tester Action	GPS - PPE;Sales order creation;S-PPE-001-028#SO - BULK - M2 Israel	Closed	TP_S4_W1SP3_PLAN TO REALIGN
8000009315	Wait for Defect Correction	GPS - PPE;Sales order creation;S-PPE-001-001#SO - PROMO - M1 Japan	Closed	TP_S4_W1SP3_TREASURY
8000009316	Confirmed	GPS - PPE;Sales order creation;S-PPE-001-002#SO - PROMO - M1 Spain	Closed	TP_S4_W1SP3_TREASURY
8000009316	Confirmed	GPS - PPE;Sales order creation;S-PPE-001-002#SO - PROMO - M1 Spain	Closed	TP_S4_W1SP3_TREASURY

Thanks again 🙂