Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 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?
Solved! Go to Solution.
Hi @dgadzinski ,
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.
Hi @dgadzinski ,
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |