Forum Discussion
Helper IIHelp with creating a new measure or column
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
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 _resultHi,
Thank you for your message, and please check the below picture and the attached pbix file whether it suits your requirement.
5 Replies
- Jihwan_Kim
Super User
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
grkanth81Thank 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
- Jihwan_Kim
Hi,
Thank you for your message, and please check the below picture and the attached pbix file whether it suits your requirement.
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:
For Measures (usually numeric calculations):
Identify What You Need:
- Determine what kind of calculation or analysis you want to perform. For example, you might want to calculate the total sales, average price, or growth rate.
Use Formulas:
- Look for a "Formula" or "Calculation" option, depending on the tool or software you're using.
- Write a formula using basic math operations like addition, subtraction, multiplication, and division. For example, to calculate total sales, you might add up the values in the "Sales" column.
Apply and Save:
- Apply the formula to create your new measure.
- Save or update the data table to make the new measure a permanent part of your dataset.
For Columns (usually text or categorical data):
Define the New Column:
- Decide what information you want in your new column. This could be a combination of existing columns, a conditional statement, or any other relevant data.
Create the Column:
- Find an option like "Add Column" or "Create New Field" in your tool.
- Specify the logic or conditions for the new column. For instance, if you want a column indicating whether sales are above a certain threshold, you might create a column with values like "High" or "Low."
Confirm and Save:
- Confirm your choices and save the changes to the data table.
Example:
Let's say you have a "Quantity" and "Price" column, and you want to create a new column called "Total Sales."
For Measures:
- Formula: Total Sales = Quantity * Price
- Apply: Multiply the values in the "Quantity" and "Price" columns for each row.
For Columns:
- New Column Logic: If Total Sales > 1000, then "High," else "Low."
- Create Column: Add a new column with these conditions.