Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
Can you please provide your suggestions to create a new measure or column in the below scenario ?
I have created a dummy data set which has some columns and values. I would like to create a new measure or new column with the below formula :
(SUM of Hours worked / SUM of FTE / No of months ) * 12
So I would like the result to be shown as grouped by period as below:
Period New Measure value
202401 71.61
Here is the breakdown for 202401 based on the above formula:
Sum of hours worked = 18.5
Sum of FTE = 3.1
No of months = 1
So it should be - (18.5/3.1/1)*12 = 71.61
Period | No of Months | EmployeeCode | FTE | HoursWorked |
202401 | 1 | 100 | 0.6 | 1.1 |
202402 | 2 | 100 | 0.6 | 2.1 |
202403 | 3 | 100 | 0.6 | 0.7 |
202404 | 4 | 100 | 0.6 | 2.4 |
202405 | 5 | 100 | 0.6 | 0.9 |
202401 | 1 | 128 | 1 | 8.7 |
202402 | 2 | 128 | 0.75 | 5.2 |
202403 | 3 | 128 | 0.8 | 3.8 |
202404 | 4 | 128 | 0.8 | 2.9 |
202405 | 5 | 128 | 1 | 7.1 |
202401 | 1 | 222 | 1 | 8.7 |
202402 | 2 | 222 | 1 | 5.2 |
202403 | 3 | 222 | 1 | 3.8 |
202404 | 4 | 222 | 1 | 2.9 |
202405 | 5 | 222 | 1 | 7.1 |
202401 | 1 | 547 | 0.5 | 0 |
202402 | 2 | 547 | 0.5 | 0 |
202403 | 3 | 547 | 0.5 | 0 |
202404 | 4 | 547 | 0.5 | 0 |
202405 | 5 | 547 | 0.5 | 0 |
I have created a measure for sum of hours worked / sum of FTE. But I am a bit struck as to how to include No of months in that measure.
Thanks,
Rav
Solved! Go to Solution.
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file whether it suits your requirement.
Expected result measure: =
VAR _workhours =
SUM ( Data[HoursWorked] )
VAR _fte =
SUM ( Data[FTE] )
VAR _noofmonth =
SELECTEDVALUE ( Period[No of Months] )
VAR _result =
DIVIDE ( DIVIDE ( _workhours, _fte ), _noofmonth ) * 12
RETURN
_result
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Thank you for your message, and please check the below picture and the attached pbix file whether it suits your requirement.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Creating a new measure or column in a data table involves adding a calculation or combining existing data to get a new piece of information. Here's a simple guide:
Identify What You Need:
Use Formulas:
Apply and Save:
Define the New Column:
Create the Column:
Confirm and Save:
Let's say you have a "Quantity" and "Price" column, and you want to create a new column called "Total Sales."
For Measures:
For Columns:
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file whether it suits your requirement.
Expected result measure: =
VAR _workhours =
SUM ( Data[HoursWorked] )
VAR _fte =
SUM ( Data[FTE] )
VAR _noofmonth =
SELECTEDVALUE ( Period[No of Months] )
VAR _result =
DIVIDE ( DIVIDE ( _workhours, _fte ), _noofmonth ) * 12
RETURN
_result
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thank you Jihwan. That worked. I also need to get the average of that measure. I know how to get the average if the value is numeric but how to get the average of the created measure in this case ?
Thanks,
Rav
Hi,
Thank you for your message, and please check the below picture and the attached pbix file whether it suits your requirement.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thank you Jihwan. That worked.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
71 | |
37 | |
21 | |
19 | |
15 |
User | Count |
---|---|
125 | |
33 | |
27 | |
24 | |
23 |