Sum Table Column of Lookup values - Paginated Report in Report Builder

[nhooper]

Hi, 

New to PowerBI, using the Report Builder

I'm trying to create a report that can audit a collection of lessons for a course completion.

I have a table that retrieves the required lessons for a given course, and uses a lookup function to check weither a user has completed that lesson from another table. The problem is I cannot figure out how to get a sum of the column containing the result of the lookup values to determine how many lessons the user has completed related to the given course.

[v-yalanwu-msft · Accepted solution]

Hi, @nhooper ;

Sorry, I cannot test in PowerBI Report Builder due to Liscense, but in PowerBI Desktop, you can create a measure as follows and then use the Matrix visual object.

Complete = 
var _a=SUMMARIZE(FILTER(ALL('Table'),[User]=MAX([User])),[Lesson])
var _b=SUMMARIZE(FILTER(ALL('Course'),[course]=MAX('Course'[course])),[lessons])
var _percen=FORMAT( COUNTROWS(INTERSECT(_a,_b))/COUNTROWS(_b),"0.00%")
return IF(ISINSCOPE('Course'[lessons]), IF(MAX('Course'[lessons]) in _a,"Yes","No"),_percen)

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nhooper]

Have not had the chance to watch the videos yet, however I am using PowerBI Report Builder, not PowerBI Desktop. 

I've attached an example in a spreadsheet of what I'm trying to acheive.

[amitchandak]

@nhooper , Not very clear. Refer if these can help

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.