Forum Discussion

gp201641's avatar
gp201641
Frequent Visitor
3 years ago
Solved

DISTINC COUNT (Interaction issue)

   Hi Everyone; I am new to using BI Power and appreciate your support on one matter. I like to create a bar chart table which shows Selected Defect Per Unit by week. Defect Per Unit (DPU): you ha...
  • gp201641's avatar
    gp201641
    3 years ago

    Hi All who tried to help, 

    I finally found the solution -  Below formulat is What I have been adivesed and worked perfectly. 

     

    DPV RATE = COUNTA('DEFECT DATA BASE'[Defect])/CALCULATE(DISTINCTCOUNT('DEFECT DATA BASE'[Job Number ]),ALLEXCEPT('DEFECT DATA BASE','DEFECT DATA BASE'[WK Number]))
     
    I hope this also may help someone in the future.
tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi gp201641 

To calculate how many different products, use = COUNTROWS(ALL('DEFECT DATA BASE'[Job Number ]))

gp201641's avatar
gp201641
Frequent Visitor
3 years ago

Hi tamerj1 , I have tried this and it counts the job all job number does not count the disrict, but when i put it in to formulation to calculate DPU, the number again changes when you filter the defect.  

 

Please see the below - I do not want product count to change depending on defect choosen.

 

Defect count will change depending defect type but product count should remain same in the formulation to ge through represantative. 

 

Forexample: 100 product were produced and only 1 product has x10 dirt defect on it. 

Currently when I Filter the dirt =  calculation is 10/1(product) = 10, but i should actually be 10/100(product)

 

 

 

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    gp201641 
    Please try

    =
COUNTROWS (
    CALCULATETABLE ( 
        VALUES ( 'DEFECT DATA BASE'[Job Number ] ), 
        REMOVEFILTERS () 
    )
)