I have 2 source. The first source is Share Point and the second 4 files deliverd by Product Owners in one Table. The Excel files are provided throught Product Owners and I have to check if I received the data from the Product Owner in a month. If is in Sharepoint status "implemented" then I have to see in the excel file if it was actually delivered.
In this case I used this measure and it is working:
received product = var _version = MAX('SHP Data' [Product_Name]) var _product = SELECTCOLUMNS(FILTER('SHP Data', 'SHP Data'[Status] = "Implemented"), "_product", 'SHP Data'[Product_Name]) var _product2 = SELECTCOLUMNS(FILTER(ALL('Excel file'), ('Excel File'[Product_Name] = _version, "_product", 'Excel File'[Product_Name] RETURN COUNTROWS(INTERSECT(_product, _product2)) |
Sample data and columns below:
| Product_Name | Group | Version | Technology | Status |
| P1 | Gr 1 | 01.01 | T1 | Implemented |
| P2 | Gr 1 | 01.28 | T2 | Planned |
| P3 | Gr 2 | 01.05 | T3 | Planned |
| P4 | Gr 2 | 01.12 | T4 | Implemented |
| P5 | Gr 3 | 1.0 | T5 | Implemented |
| P6 | Gr 3 | 3.1 | T6 | Implemented |
| P7 | Gr 3 | 3.07 | T7 | Decommissioned |
| P8 | Gr 4 | 5.04 | T8 | Decommisioned |
| P9 | Gr 4 | 01.02 | T9 | Implemented |
| P10 | Gr 4 | 02.24 | T10 | Planned |
| P11 | Gr 4 | 02.10 | T11 | Planned |
| P12 | Gr 5 | 1.02 | T12 | Implemented |
Excel File:
| Product_Name | Group | Version | Technology |
| P1 | Gr 1 | 01.01 | T1 |
| P2 | Gr 1 | 01.28 | T2 |
| P4 | Gr 2 | 01.12 | T4 |
| P7 | Gr 3 | 3.07 | T7 |
Now I need to calculate:
How many products have already been delivered by the group?
The results must be expressed as a percentage.
I tried with this measure but it display incorrect value:
Have been delivered = var _count = CALCULATE(COUNT('SHP Data'[Product_Name]),ALLEXCEPT('SHP Data','SHP Data'[Group])) var _delivered = CALCULATE(COUNT('SHP Data'[Product_Name]),FILTER('SHP Data','SHP Data'[received product] = 1 ))
return DIVIDE(_delivered,_count) |
Could you help me with calculation, please?
