Using distinct values instead of the average in calculations

[Anonymous]

Team

I am trying to create analysis that compares forecasted billable hours to actual billable hours and provide a variance. I'm having issues with my Forecasted Billable hours metric because I have employees staffed on 2 or more projects with different billable rates for each project.

.Forecasted Hours Measure = sum('Forecast'[Forecasted Hours])

.Forecasted Revenue = [.Forecasted Hours Measure]* AVERAGE('Bill Rate'[Billable_rate])

Currently, Forecasted revenue is calculated by taking the sum of the Forecasted Hours column and multiplying it by the "average" of the Bill Rates column. I used average because within the Bill rate table is a billable rate column that has the rate duplicated many times. Its duplicated many times because it's a referencing its source, the Actual hours table, where a row is created every time my employees log an hour. Bill Rate (column) is part of the Actual hours metadata.

I used "average" so that PowerBi would "average" the records together and not sum them and skew the numbers. What I actually want is to somehow ignore duplicates. I don't want a weighted average.

My problem reveals itself when I try to report Forecasted Revenue. My forecasted revenue number is calculated based on the average Billable rates of all the projects an employee is staffed on and the number of hours they forecast. Right now, It doesn't look at each project as a distinctly separate bill rate. It averages all of the bill rates by the person. I don't want that. 

For example.

Person 1 could be staffed on Project A and Project B. Project A has a bill rate of $50 dollars an hour and Project B has $100 dollars an hour. If Person 1 forecasts to work 10 hours for both projects in a week, I'd expect my forecast to be $1500. But because my data is set up such that bill rate is grabbed when hours are entered I could have an instance where there are 3 Project A entries and 1 Project b entry and the system thinks my bill rate is 62.5 for a forecasted revenue of 1250.

I don't know how to get the system to only look at the distinct values. Do you have an idea of how I should think about this?

[v-xicai]

Hi @Anonymous ,

You can try to create measures like DAX below, assuming every person has an employee ID.

.Forecasted Hours Measure= CALCULATE(SUM('Forecast'[Forecasted Hours]),FILTER(ALLSELECTED('Forecast'), 'Forecast'[Employee.ID] =MAX('Forecast'[Employee.ID])&&'Forecast'[Project.name] =MAX('Forecast'[Project.name])))

.Forecasted Revenue = [.Forecasted Hours Measure]* CALCULATE(SUM('Bill Rate'[Billable_rate]),FILTER(ALLSELECTED('Forecast'), 'Forecast'[Employee.ID] =MAX('Forecast'[Employee.ID])&&'Forecast'[Project.name] =MAX('Forecast'[Project.name])))

Or could you please share your sample data or desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Amy

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

[Anonymous]

Is there an easy method to mask sensitive data? My pbix has thousands of lines of sensitive data... I'm not sure how to share it without hours spent changing the data. Are you aware of a tool that does it? 

[v-xicai]

Hi @Anonymous ,

You can create your sample data such as 1,2,3,,etc on, especially to show the fields, tables and the relationships between these key tables in excel screenshot or in pbix file.

Best Regards,

Amy

[Anonymous]

Hey Amy, @v-xicai 

Playing around with the example you gave me, I think it gets me very close! One thing it doesn't do is show me the total anymore. The numbers are all correct in the table but there no longer a total line. Is there a way to write this expression that give a total? 

[Cmcmahan]

If you're using a matrix or table visualization, you can make the totals sum differently than the individual values.  See this post for an accounting of my adventures trying to solve this problem.  The resulting trick is to create a measure using IF(ISFILTERED()) to do one thing if the lowest level grouping is filtered vs when it isn't filtered.

[Anonymous]

@v-xicaiand @Cmcmahan 

I never could figure this out. So I spent the time recreating my data in a test file. 

This could be an issue with my summarization, relationships or just general dax but I want the totals to sum the numbers that are listed in the columns. The columns are pretty straight forward with the math they are doing. I have Actual Hours, Forecasted hours, A bill rate (grabbed from a bill rate table). I use the Bill rate and multiply it by the Forecasted hours and the Actual hours to get a Forecasted and actual revenue. Then I subtract those two to get a variance. 

There is something in this overall lesson that I am not understanding. Let me know if this makes perfect sense to you two. Also, Thank you very much for your help.

https://1drv.ms/u/s!As5mLIpCCmxIjjJWqCC7xzIgB07w

Let me know if that Link doesn't work. All data has been masked. 

[Cmcmahan]

So I've gone and set up one measure that works in a table, and one that works in a matrix to get your desired result.  I personally think that a matrix is a cleaner way to display the data, but that's a personal preference.

Here's my .pbix file.

A few things I did:

• Cleaned up your [.forecasted hours measure] to return blanks when there's no data.  This removes rows of blank data from your table.
• Changed the date in the table to a single value instead of a hierarchy.  Splitting all that out was too much
• Set up a relationship between your Weeks table and Bill Rate 2 table so that the slicer would apply to Spent_date
• Created a [BillableRateForTable] and [BillableRateForMatrix] measures.  Use these instead of the raw average of Billable Rate in tables/matrix

Here's the code I used for the matrix version.  The table version is identical, but it uses HASONEVALUE('Users'[Full Name]) instead of ISINSCOPE, since tables don't have a scope.

BillableRateForMatrix = IF( ISINSCOPE('Bill Rate 2'[Person]), 
                            AVERAGE('Bill Rate 2'[Billable_rate]), 
                            SUMX( SUMMARIZE('Bill Rate 2', 
                                            [Person.id], [Client.id], [Project.id], 
                                            "Avg Bill Rate", AVERAGE('Bill Rate 2'[Billable_rate])), 
                                  [Avg Bill Rate]))

As a quick note, when you're selecting exactly one date with a slicer, there's only one value being used in your average billable rate calculation.  I assume you eventually want to set it up with a date range.

Hopefully this is the result you're looking for, and my changes make sense.  If you have futher questions, feel free to ask.

[Anonymous]

This is very progressive. Thank you so much. 

Date - I'm trying to tie individual days to a specific week. For example, days July 1, 2019 to July 6, 2019 tie back to week July 1, 2019. So in the slicer, when I select that day, it aggregates each of those days. I probably just need more time, but I'm not sure how to make that happen. 

Forecasted Revenue - I haven't had a chance to fully understand the ripple effects of your bill rate formula but ultimately, I'm looking for the Forecasted revenue of around $71,965 for the week of July 1 to July 6. For the week of July 24th, I'm looking for $97,040. 

Bill Rate - Bill rate has been tricky for me to figure out. I originally eliminated the Spent Date field and simply deleted the duplicate for Person ID, Client ID, Project ID and Task ID. That got me really close but there were still a couple of people who's Bill rate was an average (and I couldn't figure out why). 

Ultimately, I need to have Actual Revenue be Bill Rate * the actual hours recorded each day. I only have forecast data at the week level so I need forecast to be Bill rate * total hours forecasted for each person for each individual project. Then I need the sums in the summarization total bar to represent =sum(column Forcasted Revenue) or =sum(Column Actual Revenue). I can do this in excel really easy but for some reason I can't wrap my head around it in PowerBi. Then I need my Forecast / Actual Variance field to subtract the two to show me the variance between plan and actuals.

I agree with you that the Matrix view is cleaner and easier to use. I completely agree. 

I'm trying to get to a point where I can post this chart in the cloud for end users to see how they did but right now I can't because the summarization totals are different than the sum of all of the numbers shown in the column. I can certainly see it when I export the column to excel and sum the columns but can't assume my end user knows how to do that. 

[Cmcmahan]

You're actually really close to what you want I think.  When I went and monkey'd around with your visual, I tried to make minimal changes, especially to things like data structure.

Let me know if there are more, but it seems that your outstanding wishlist items are: 

• User picks a date, visuals show aggregation from that week instead of the one day
• Fix the bill rate - this seems like a data source problem.  
• Display forecasted and actual for any specific week picked.

So let's tackle these issues one at a time.  First let's start with the bill rate.  You say that you've gotten it correct for most people, but a few people have weird averages instead of a flat rate.  For a first pass, I would ignore changes on bill rate within a single week, and come back to how to solve the problem once you have it working for 99% of the summaries.  The big issue that's going to throw off your forecast is that if the values going into the average have large variation, your output will be off if they do a lot of work as one billable rate that's far from the average.  You can actually use power bi to associate a bill rate with a project (which it looks like you've already done to an extent). Looking at the data I have, from Jul1-Jul6 the bill rate for each person is the same.  So I'm going to simplify the solution, and ignore bill rate average for now.

The next issue, where a user picks a date and you want to show data from that week, as opposed to that day. You've gotten very close to the solution I would use with your Weeks table.  You've stumbled upon the idea of a date dimension. Date dimensions make slicing and filtering based on different attributes of a day incredibly easy. The issue is that your Weeks/Weeks2 tables didn't go far enough. So let's go through and make a full date dimension. Your week seems to start on Monday and go through Sunday, so I would create a full date dimension following this guide, making sure to change the Date.WeekOfYear expression to call out Monday as the start of your week.

For your case, in order to make your slicer play nicely, I would potentially consider adding a new column to the date dimension "StartOfWeekDate"  where you use Power Query or DAX to create a column that is the first day of that week.  Then you put StartOfWeekDate in your slicer, and it will only show one date per week but let you aggregate data from all the days in that week. 

In order to simplify the dataset so that I could ingest what was happening with the numbers, I set up a filter to only show Will Lighthouse's data, since he worked on 2 projects on the July 1 week.  Our table measures from before break a little bit in the table format because we're filtering down to one name, and they're expecting multiple names.  So I'm going to use the matrix, which is a bit more reliable since it uses ISINSCOPE.

The big issue is how billable rate is being calculated.  Right now you're getting a sum of billable rates by project&person, and multiplying that by the sum of forecasted hours, but that means you're getting a HUGE number for forecast, because the rate is a sum of all rates.  In this case, summing Will's rates gives a value of 340, which is getting multiplied by the forecasted hours of 32, giving an forecasted revenue of over 10k.  So I changed it to use an average within a category, and suddenly the numbers start making sense.  I got a forecasted revenue of $75,739.76 and an actual revenue of $60,761.00 for the week of July 1.  I don't have data from you for the week of July 24, so I can't double check my answer against that expected result.

I also updated your variance measure to compare actual revenue and forecasted revenue, instead of using forecasted hours. At this point, it looks like your math/start of week just needs minor tweaking to come into line with your expected results. It even seems to average 

Here's my current .pbix file. Play with it and if any of the numbers don't make sense, please share the result, and what you expect the result to be. 

[Anonymous]

@Cmcmahan 

This is sooo close..... But the matrix's summarization lines don't always add up to the the sum of the numbers that encompass them. For example, for Technology Corp - Logistics Strategy, If I add the forecasted revenues up, it should give me $21K (Jeremy P is 9k, John T. is 3.6k, Tim T is 6k, and Will L is 2.4k). They are all round numbers but some how I can't figure out how to get Power Bi to vertically sum things like an excel pivot chart. Its treating the summary line like its own calculation.... And if it does that, I don't know how to make it right. Does that make sense? 

Essentially, the Summary line for each project should be the exact sum of all of the numbers above it. So Technology Corp should be $21K, Nebraska Enterprises Tool selection should be $14K.... And so on. That is where I am stuck.

I absolutly love the matrix view over the table view. You were SO incredibly right about that. Thanks for teaching. 

[Cmcmahan]

You're exactly right in thinking that the visual is treating the summary line like it's own calculation. That's exactly what it's doing.  The issue is that you want the summary to calculate differently than each individual row, so you get to funk.  The problem is that it's "weighting" every billable rate the same instead of by the number of hours. 

So for the Techo Corp - Logistics calculation, it's getting an average of all the billable rates, and multiplying that by the sum of all of forecasted hours.  We can edit the calculation using a weighted sum formula, which takes the general form of SUMX(Table, Table[Amt] * Table[Weight]).

So for this case, we change our forecasted Revenue measure, adding the sections in blue:

.forecasted Revenue Measure = SUMX('Users', [.forecasted hours measure] * [BillableRateForMatrix])

By using SUMX, we're making Power BI use each applicable row of the 'Users' table individually (so each person) and getting a result from the two measures and storing the results in memory to sum for the total line.  This is also likely to give you better answers in the case that a person has different billable rates within the same project.  The downside is that if you have a large amount of data, this DOES cause a performance loss, since for each total calculation DAX is doing a calculation for each person within that category and storing that information separately to sum later instead of doing quicker aggregations on the whole population.

Hope this has been helpful! It's been a fun problem to think about.

[Anonymous]

This is so close but it revealed a scenario that I did not know I needed to model. 

The only instance where it is not modeling my data correctly is when I have a client with 3 different projects. Every other Project has 1 project per client and my numbers tie out perfectly. The reason why we didn't see it in our test that you helped me with was because I don't think I had this client actually have 3 projects (albeit, 2 of the projects don't have any data saved to them). 

1. At the project level, everything is perfect. So for clients that only have 1 project, the total for the project and the total for the client will be the same. In this ONE case, where a client has 3 projects, there is an incorrect number. Thus, my grand totals don't match. 

Does this make sense? Should I model an example? WE are so close to putting 2 months of not knowing to rest. 

2. I'm also noticing that my Grand total line is not a sum of all of the projects a the project Level. I was able to notice this by archiving the 2 projects above that didn't have any data, thus, aligning all of my projects. Imagine that they are ALL good now. My bottom line grand totals don't line up. Could you imagine why? Should I build another model to show you what I'm looking at or is it something you already know? 

Thank you again for your help.

[Cmcmahan]

Honestly, if you've only got one case that's weird with multiple projects under a client, would it make sense/be doable to just split this one client into "Client Corp - 1", "Client Corp - 2", "Client Corp - 3" so that PowerBI treats it as 3 different clients with one project each? Or just give "Client Corp" 3 separate clientIDs with the same client name  It could be  a lot of work to debug this one rare scenario otherwise. 

As far as the bottom total line not coming out equal, I have no idea what's causing it.  Is the result high or low? A little or a lot off? Is the total for each project correct, but the grand total wrong?  Is it wrong for every date period, or just a couple of them? 

My shot in the dark guess at this point is that when you're grand totaling, the [Billable Rate for Matrix] measure is coming up with weird results, since each person might have multiple rates across different projects, so it's averaging/summing those in unexpected ways. At this point, since we know that (for most cases) there's one project per client, we may be able to simplify that logic to work in a more predictable way when there are multiple bill rates associated with a single person. 

Another model that demonstrates the issue(s) would be helpful. 

[Anonymous]

@Cmcmahan The Grand total always seems to be high. You can see it in your data that you gave me. The grand total is not the sum of the projects above it. Everything you did makes sense but for some reason, grand total isn't summing correctly.  Bill rate for matrix seemed solid so I'm stumped. 

Take a look at Forecasted Revenue column and add up the subtotals... You don't get what the grandtotal number is. 

As for the other one... If I can solve for the grand total, I can work around this one for now. I'm preparing a dashboard for none technical stakeholders so these numbers have to be painfully simple. Unfortunately with number 1, this puts me at square one. I've always been able to get my projects to total but they don't grand total. This is soooo close

[Cmcmahan]

After the most recent update to [.forecasted Revenue Measure], the grand total seems to be correct. 

I totaled up the forecasted revenue column, and it matched the grand total at the bottom.  Is there something I'm mussing?

[Anonymous]

@Cmcmahanor there may be something I've missed. 

Could you shoot me another link to your latest version? 

[richbenmintz]

Hi @Anonymous , 

I know that this is a simple solution, but have you tried

Forecast Rev.  = SUMX('Forecast', [Forecasted Hours] * RELATED('Project Master'[hourly_rate]))

essentially SUMX acts as an iterator and will multiply each row in the forecast table by the related hourly rate, then sum up the results.

Hope this helps.

Richard Mintz

---

@Anonymous wrote:

@Cmcmahanor there may be something I've missed. 

Could you shoot me another link to your latest version? 

---

[Cmcmahan]

Here's the latest I have:

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

[Anonymous]

Thanks! 

Take a look at June 24th and June 17th

On June 24th, the subtotals come out to 94,640 but the Grand total says 97,333.33. 

On June 17th, the subtotals come out to 99,015 but the grand total says 99,300.83

I think the easiest way to look at this is, if its got a decimal in the Grand total, something is amiss. The BillRateforMatrix function is the only thing i can think to change but It stumped me over the weekend. It looks just fine... I'm missing something 

[Cmcmahan]

Ahh, I see what you're talking about.   It seems to only be when a few different companies are in the mix that an issue comes up.  Just sticking with the June 24 data, it seems when you mix Balcony Enterprises, Nebraska Enterprises, and Zenith in any combination, the total comes out wrong.  So let's see what's up with those companies that may cause issues.

Individually, the numbers for each project add up correctly, so it must be something in the way we total them. Let's go back to what's actually being calculated.

The forecasted revenue is:

.forecasted Revenue Measure = SUMX('Users', [.forecasted hours measure] * [BillableRateForMatrix])

The forecasted hours is:

.forecasted hours measure = IF( SUM(Forecast[Forecasted Hours]) <> 0 && [BillableRateForMatrix]<>BLANK(), SUM(Forecast[Forecasted Hours]), BLANK())

And the billable rate is:

BillableRateForMatrix = IF( ISINSCOPE('Bill Rate 2'[Person]), AVERAGE('Bill Rate 2'[Billable_rate]), AVERAGEX(SUMMARIZE('Bill Rate 2', [Person.id], 'Bill Rate 2'[Client.id],'Bill Rate 2'[Project.id], "Avg Bill Rate", AVERAGE('Bill Rate 2'[Billable_rate])), [Avg Bill Rate]))

My guess is the BillableRateForMatrix is causing issues.  If there's not a single person in scope, it gets the average of bill rates grouped by client, project, and person.  Usually with one client to one project, it groups them by person and averages them, so it works just fine. By slicing for Balcony&Zenith as well as Angela Shepherd, we can see the problem more clearly.  She has no forecasted hours in the Balcony project, and a different billable rate, so her rate is averaged to 192.50 multiplied 18 forecasted hours for a bad result.  The same issue with Justice Jingle and only showing data for Balcony&Zenith.

So I was messing around with the forecasted Revenue, to see if we could change the SUMX expression to work when iterating across multiple projects/billrates effectively.  Right now, it goes through each person and groups all of their data together.  I tried changing it to:

.forecasted Revenue Measure = SUMX('Bill Rate 2', [.forecasted hours measure] * [BillableRateForMatrix])

Which gave correct sub/grand totals, however this caused blanks in the individual rows which were reflected in those totals. 

I've got some meetings to attend until the early afternoon, but play with it and see if you can figure out a good way to get correct totals.  I'll take another look at it this afternoon and see if I can mess around with the [BillableRateForMatrix] to get the context correct.