Is that what you wanted?
First connect both the sources and blend with Client ID field
Now for the first output just drag the fields from Primary source first then Amount from secondary source.
For the final output you need to create few table calculation and set the data partition based on Client ID
Drag Number of Records and apply running sum to get the total records
Total amount: WINDOW_SUM(SUM([Secondary].[Amount]))
Total Avg: WINDOW_AVG(AVG([Secondary].[Amount]))
Drag these fields in canvas
You need to create one final calculation and filter some unwanted values to get the final output
Filter: LAST()=0 and compute using Client ID
Workbook attached for your reference. Let us know if this help.
Note: Edit the aliases of the measure names as per requirement.
Blend_v10.2.twbx 19.8 KB
Thank you so much for taking the time to help me out with this problem. This is exactly what I needed and have spent today successfully incorporating into my workbook.
I have learnt alot today so thank you! I work for a regional hospital and this piece of work will help us understand our patients better.
many thanks Tricia
Thanks for your kind words Tricia! Much appreciated such compliments really encourage me to help you guys! Have a good day ahead.