Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Hello all,
I have a measure that works all great.
The measure takes the deadline of projects and based on this, it calculates the number of working days and multiplying with the hourly rate of the employee and the expected working hours per week.
Here it is:
=-- Today, brought forward from Power Query
VAR filteredMin =
MIN('Calendar'[Date])
-- Set by the client, forecasting period
VAR filteredMax =
MAX('Calendar'[Date])
-- Deadline of Project
VAR deadlineProject =
MAX(Quote_Items[Deadline])
-- Repeating deadline of Project in all months.
VAR deadlineWhole =
CALCULATE(
MAX(Quote_Items[Deadline]),
ALL('Calendar'))
-- Calculation of accurate date to be used to calculate working days.
VAR deadlineMonth =
IF(
ISBLANK(deadlineProject),IF(
deadlineWhole < filteredMax,BLANK(),filteredMax),
deadlineProject)
-- Filter the 'Calendar' table to include only working days between filteredMin and deadlineMonth
VAR workingDaysCount =
COUNTROWS(
FILTER(
'Calendar',
'Calendar'[Date] >= filteredMin &&
'Calendar'[Date] <= deadlineMonth &&
WEEKDAY('Calendar'[Date], 2) <= 5 -- Weekday function, with 2 means Monday = 1, Sunday = 7. Excludes weekends
)
)
-- Returns the hours worked per week on that project
VAR maxHours =
CALCULATE(MAX(Quote_Items[Hours per week]),ALL('Calendar'))
-- Returns the rate of the employee for this project
VAR rateOfEmployee =
CALCULATE(MAX(Quote_Items[Rate]),ALL('Calendar'))
-- Final result, multiplying by the working days.
VAR result =
workingDaysCount * maxHours * rateOfEmployee
RETURN result
The problem I'm facing is that it doesn't calculate Totals correctly (which makes sense, I think i should SUMX each of the values).
Any ideas on how I can change this measure to return the correct SUM?
Many thanks
Hi @kalspiros ,
First of all thanks to Rupak_bi for the quick reply. I have some other thoughts to add:
You can create a new measure to be placed on the visual object instead of the original measure, totaling the correct display.
New Measure = SUMX(VALUES('Table'[Deadline]),[Measure])
If the above one can't help you get the desired result, please provide some sample data. It is better if you can share a simplified pbix file.
Refer to:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for your reply and providing these helpful pages.
In all honesty, I'm working on Excel.
I have just recreated the model in PBI but my original measure does not work as expected.
Therefore, I'm providing link to both files and I'd be probably more interested on exploring the .xlsx instead.
Your measure didn't quite work as expected I'm afraid.
Please feel free having a look and let me know if there's anything more I can do to assist.
Many thanks
Kind Regards
Hi @Rupak_bi
Thank you for your message. Actually 'sum' won't do the cut I'm afraid.
workingDaysCount is one of the culprits as it'll return an ubnormal count of days in the Grand Total.
Additionally, a sum of rateOfEmployees won't return the accurate number as it depends on how many hours each of the employee will work on each project.
Please find attached the file with some sample data and the measures (mainly interested on "Price")
Many thanks
PS: not sure how i can share sample file and couldn't find anything relevant here: https://community.fabric.microsoft.com/t5/Using-the-Community/tkb-p/fbc_using_the_community
Hi @kalspiros ,
To get correct sum, you need to replace the "max" functions to "sum" function whenever applicable based on the operations you are performing in each variables. review all the calculate expressions and modify to ge the righr total.
Else share sample data.
User | Count |
---|---|
25 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
40 | |
28 | |
28 | |
22 | |
21 |