Help with creating a new measure or column

[grkanth81]

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

[Jihwan_Kim · Accepted 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

[soniya-01]

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):

1. 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.

2. 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.

3. 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):

1. 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.

2. 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."

3. 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.

[Jihwan_Kim · Accepted 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

[grkanth81]

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

[Jihwan_Kim · Accepted solution]

Hi, 

Thank you for your message, and please check the below picture and the attached pbix file whether it suits your requirement.

[grkanth81]

Thank you Jihwan. That worked.