non trivial DISTINCOUNT with multiple filters

[Anonymous]

Dear members, 

i've got some headaches with calculation which is looks easy to calculate by eyes and impossible for me to get correct result in PBI. 

Sample data: 

wetransfer: https://we.tl/t-vSg93UKQcP

google drive: 

https://drive.google.com/file/d/1qCpnXZr_X858nYubD0JVehnpagmz6mHv/view?usp=sharing  - pbix

https://drive.google.com/file/d/1pBpQFhQblp8aXsIemvcEB5RwpdqZWlhI/view?usp=sharing - Excel

Goal: to get correct claims statistics and their visualization

Data description:

• Presented data file contains dates of claims receving, order number, material code (SKU), investigation result and column which is explain is order taken into KPI calculation or not.
•  investigation result is checked and resulted on SKU level: for instance, recevied claims for the order 53901 contains 6 SKUs which are confirmed or not
•  if order number is 0 that means mentioned SKUs are belong to order number which is the latest before 0: row 234 is belong to row 233 as of Order number

Rules for KPI calculation: 

• KPI is calculated based on taken (column Claims taken for KPI calculation or not) and order qty (meaning: if some any of SKUs in claimed order is confirmed  then whole order is confirmed and should be calculated as  confirmed claim) per period (year, month, day)

Obstacles/headaches:

1. how to correctly calculate it? 

using:

Nr of confirmed orders = CALCULATE(DISTINCTCOUNT('Sheet1'[ORDER number]);FILTER('Sheet1';'Sheet1'[ORDER number]>0&&'Sheet1'[Claim is taken for KPI calculation or not]="taken"&&'Sheet1'[Investigation result]="confirmed"))

Nr of NOT confirmed orders = CALCULATE(DISTINCTCOUNT('Sheet1'[ORDER number]);FILTER('Sheet1';'Sheet1'[ORDER number]>0&&'Sheet1'[Claim is taken for KPI calculation or not]="taken"&&'Sheet1'[Investigation result]="not confirmed"))

i have got oucome that the same order is calculated as confirmed and not confirmed due to some of its SKUs are confirmed and not confirmed (as an example above, order 53901 )

2. Using Prompt page i'd like to return order numbers for the visualized table, but it shows all received claims and not only confirmed or not confirmed - please advice how to fix and where i'm wrong.

is it possible somehow to calculate correct results for 2 questions?

thank you in advance.

[MFelix]

Hi @Anonymous ,

The link you provide does not have any file can your check it please.

Besides that do you have any other tables on your model or just the one you present?

[Anonymous]

Hi @MFelix 

https://dropmefiles.com/AdxlX - data itself.

the sample pbix contains only 1 table. the main pbix contains several like calendar, list of clients, etc, but they're not relevant and useful for mentioned cases. 

p.s. i'm very sorry, but i dont know how to upload files in this post

thank you in advance.

[MFelix]

Hi @Anonymous ,

Not sure if it's me but the link does no have any files.

Can you do it in a we tranfers or google drive link?

[Anonymous]

Hi @MFelix 

yes, sure

wetransfer: https://we.tl/t-vSg93UKQcP

google drive: 

https://drive.google.com/file/d/1qCpnXZr_X858nYubD0JVehnpagmz6mHv/view?usp=sharing  - pbix

https://drive.google.com/file/d/1pBpQFhQblp8aXsIemvcEB5RwpdqZWlhI/view?usp=sharing - Excel

Thank you in advance.

[MFelix]

Hi @Anonymous .

Can you please share the result you are expecting for example the first line of your table the one in the 9th?

[Anonymous]

HI @MFelix 

yes, of course, better to have the example for 22.01.2020

order 53901 is partially confirmed, but based on the rule mentioned in the first post: if 1 SKU of order is confirmed then whole order is confirmed as claim.