Reply
Frequent Visitor
Posts: 11
Registered: ‎12-05-2018
Accepted Solution

Calculating percentages with monthly and daily data

Hello, 

 

I have two tables with approx 1 million rows. Each table includes data from Jan 2018 until Present.  The "Count of Fees Waived" column is based on daily inputs.This data includes site and Agent. The "Calls Handled data" is based on monthly inputs, it includes site but does not include agent.  I am trying to calculate Monthly Count of Fee Waiver rates as a percentage of Monthly Calls Handled.  I tried to create a new measure to calculate "Monthly Fee Waiver Rates" and when I view this new measure by month the calculation is way off, it is extremely low.  I believe it has something to do with my "Count of Fee Waivers" data being daily while my "calls handled" data is monthly.  I also tried to establish a relationship between the two tables but this did not work.  My end goal is to include the Monthly Count of Fee Waiver rates in a chart that allows me to drill down to site level and agent level. Can anyone help me?

 

MonthsSiteCalls Handled
Janstanton9113
Janovertown9698
Jankingston3515
Janlinstead10810
Janmabelton13045
DateSiteAgent idCount of Fee Waivers
1/1/2018

stanton

abcd2
1/2/2018overtownadsdsd6
1/3/2018mabletondsfsf1
1/4/2018linsteadsds9
1/5/2018burchellasasa1

Accepted Solutions
Super User
Posts: 9,846
Registered: ‎07-11-2015

Re: Calculating percentages with monthly and daily data

You will need a relationship between the two based on Site. To do this, create a bridge table like:

 

Table = DISTINCT('Table'[Sites])

Alternatively, you could create a column in each table like this:

 

Column = CONCATENATE([Months],[Site])

Column = CONCATENATE(FORMAT([Date],"mmm"),[Site])

And use those as your relationship.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


View solution in original post


All Replies
Super User
Posts: 9,846
Registered: ‎07-11-2015

Re: Calculating percentages with monthly and daily data

You will need a relationship between the two based on Site. To do this, create a bridge table like:

 

Table = DISTINCT('Table'[Sites])

Alternatively, you could create a column in each table like this:

 

Column = CONCATENATE([Months],[Site])

Column = CONCATENATE(FORMAT([Date],"mmm"),[Site])

And use those as your relationship.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Highlighted
Frequent Visitor
Posts: 11
Registered: ‎12-05-2018

Re: Calculating percentages with monthly and daily data

Greg Deckler..Thank you very much that did the trick