Visualize sum of values missing from one table by date

[rhuq]

Hi PBI community, I have a need to present a visualization of telephony usage in our organisation.  I have one table with all mobile numbers assined to staff, one with landline and associated email address in another and one with every incoming and outgoing calls in the past year, with a field for caller and one for recipient I want to be able to show the % of numbers from the mobile and landline/ email table that do NOT show up in the calls table, per month.  This is to give us an idea of what number of staff don't use either their landline number or mobile.

Another visualization I would like is to maybe show the % calls each month who's numbers are certan ranges, e.g. 0-100, 101-500, 500-1000, 1000+ calls per month.

Any assistance would be appreciated, I am still learning PBI.  

[PaulDBrown]

@rhuq 

Can you please create a mockup sample dataset (in Excel for example) replicating the tables? It's hard to envisage the setup.

You can upload the sample file to a cloud service (onedrive, Google Drive, Dropbox...) and share from there.

Thanks,

[rhuq]

Thanks @PaulDBrown , I've added a sample here:

https://drive.google.com/file/d/1FsfDTnj2eXWrQ2NkvAK9-34ZKyJ0NEkI/view?usp=sharing

Each sheet replicates what I have in my tables.  In the Calls tables I use the "CleanSource" and "CleanDestination" columns, which I derived to have a consistent format for calls - landline, mobile or email (for skype calls).  Does it make sense as to what I'm trying to do?

[PaulDBrown]

@rhuq 

Thanks for the sample. I do not however see what the association is between the tables. Which columns/fields from the phone number and mobile number are associated to the calls table? (If any of course) 

What would help is if you posted a depiction of the visual you are trying to recreate (an Excel mockup for example) and which are the relevant fields from each table.

[rhuq]

Hi @PaulDBrown in the PhoneNumbers table, the PhoneNumber and Email address is linked to both the CallSource and CallDestination columns in theCalls table, as is the MobileNumber column from the MobileNumbers table.  This is because calls can come from either a landline number, a mobile number, or a skype account which is associated to the email address.

[Ashish_Mathur]

Hi,

Based on the file that you have shared, please show the exact result which you are expecting.

[amitchandak]

@rhuq , if you looking for bucketing a measure, then refer this video

https://www.youtube.com/watch?v=CuczXPj0N-k