DISTINC COUNT (Interaction issue)

[gp201641]

 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 have manufactured 10 products, and in total, there are 110 "Dirt" defect. I want to show this week that "Dirt" DPU is 110/10=11
The problem I am having is that - 4 products hasn*t got any "Dirt", so when I try to calculate DPU for "Dirt", it shows the results = 110/6 (doesn't count the 4 cars because there is no dirt defect) So this is not representing our state correctly. So I don't want the distinct Count to change by the selected Defect.

To calculate how many different products I used = DISTINCTCOUNT('DEFECT DATA BASE'[Job Number ])
To calculate how many Defect, I used = COUNT('DEFECT DATA BASE'[Defect])

To calculate DPU I used= ('DEFECT DATA BASE'[Defect Count])/([Distict Vehicle Count])

As I said, when a filtered the defect, the distinct product count changes as some cars doest have this defect.

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)

Distinct product count in the formulation should not change 😞

here is an example table 

I appreciate your support.

[v-jianboli-msft]

Hi @gp201641 ,

Please try:

Measure = COUNT('DEFECT DATA BASE'[Defect])/CALCULATE(DISTINCTCOUNT('DEFECT DATA BASE'[Job Number]),ALL('DEFECT DATA BASE'))

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

Hi @gp201641 

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

[gp201641]

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]

@gp201641 
Please try

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

[kongyuancn]

Hi @gp201641 , I think you should create a Product table which include all your product. And then add a relation between this table and your defect table. Then District Vehicle Count = DISTINCTCOUNT(ALL('Product Table'[Job Number ]))

[Anonymous]

If I understand this correctly, you can try to use DISTINCTCOUNT on the Date column, instead. It will give you leverage to choose what value to pick from, and when. Also it will count each entry for each type of dirt or not that there is. 

[gp201641 · Accepted solution]

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.

[gp201641]

Hi Thanks for the quick response, I am not sure how to DISTINCTCOUNT on the Date column " if I distinc count date " Does it not give me the "1" beacuse all are same date.

can you please tell me how to try your idea?

[Anonymous]

Right, my bad. I meant to use a COUNTROWS(), which would count all the rows you have in there, not the distinct values in them. @gp201641 's solution is also valid, assuming making such a table wouldn't be too difficult.