How many products does the group need to deliver

[Anonymous]

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:

• SharePoint:

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 does the group need to deliver, and how many products have already been delivered by the group?

The results must be expressed as a percentage.

Could you help me with calculation, please? 

[v-yadongf-msft · Accepted solution]

Hi @Anonymous ,

Please create a new column:

New_version = LOOKUPVALUE('Excel file'[Version],'Excel file'[Product_Name],'SHP Data'[Product_Name])

Create two measures:

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'[Status] = "Implemented" && NOT(ISBLANK('SHP Data'[New_version]))))
return
DIVIDE(_delivered,_count)

Need to deliver = 
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'[Status] = "Implemented" && NOT(ISBLANK('SHP Data'[New_version]))))
return
DIVIDE(_count - _delivered,_count)

I think this is the result you want:

Best regards,

Yadong Fang

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