topic Re: Help with creating a new measure or column in DAX Commands and Tips

Help with creating a new measure or column

grkanth81 — Mon, 11 Dec 2023 03:54:15 GMT

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

Re: Help with creating a new measure or column

Jihwan_Kim — Mon, 11 Dec 2023 04:13:13 GMT

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

Re: Help with creating a new measure or column

soniya-01 — Mon, 11 Dec 2023 04:33:06 GMT

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.

Re: Help with creating a new measure or column

grkanth81 — Mon, 11 Dec 2023 10:46:30 GMT

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

Re: Help with creating a new measure or column

Jihwan_Kim — Mon, 11 Dec 2023 14:33:15 GMT

Hi,

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

Re: Help with creating a new measure or column

grkanth81 — Tue, 12 Dec 2023 01:25:37 GMT

Thank you Jihwan. That worked.