Issue with DAX - Compliance results received on due time for the dashboard

[Anonymous]

Hello All, 

I have to show KRI: Complaiance results received on due time for the dashboard and I need to calculate and show the following columns:

I have 2 source. The first source is Share Point and the second are 30 excel files merged into 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. Can this be automated with dax and how to calculate column expected, received and coverage?
Sample data and columns below: 

• SharePoint:

• Excel file: 
  * 1 technology may have more than 1 product

Could you help me with this?I would be very grateful

[Anonymous]

Hello @Greg_Deckler it works, thank you! But I would like to show the total value for this on the card and pie chart and when I added this calculated measures to the card, it's blank. Could you help me with this? I really appreaciate your help. 

[v-xiaotang]

Hi @Anonymous 

Thanks for reaching out to us.

I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

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

[Greg_Deckler]

@Anonymous OK, put Technology from SharePoint table into a table visual. Then these measures:

Expected = COUNTROWS(FILTER('SharePoint',[Status] = "Implemented"))

Received = 
  VAR __Technology = MAX('SharePoint'[Technology])
  VAR __Products = SELECTCOLUMNS(FILTER('SharePoint',[Status] = "Implemented"),"__Product",[Product Name])
  VAR __Products2 = SELECTCOLUMNS(FILTER(ALL('Excel'),[Technology] = __Technology),"__Product",[Product Name])
RETURN
  COUNTROWS(INTERSECT(__Products, __Products2))

Coverage = DIVIDE([Received], [Expected], 0)