Need help with measure

[Anonymous]

Hi there!

I´m trying to get this calculation done:

Tons/Number of FTE

I have these tables:

Table1: Tons

Line|Month|Tons

Table2: Base

ID number|Month worked

What I need is to graph by month, the sum of al the tons produced divided by the total of FTE that worked in each month.

I came up with this measure:

Tn/FTE = divide( sum(Tons[Tons]), DISTINCTCOUNT(Base[ID]))

The issue here is that if i apply a filter to the graph, values will change acording to the number of FTE who worked in the filtered month, the math will do is Jan´s Tons/# filtered month ; Feb's Tons/#filetred month...

How can i solve this issue?

Thanks in advance!

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

Try this way as below:

Step1:

Create a dim date table:

https://radacad.com/do-you-need-a-date-dimension

Step2:

Create the relationship as below:

Step3:

Create a measure

Tn/FTE = DIVIDE( SUM(Tons[Tons]), DISTINCTCOUNT(Base[ID Number]))

Result:

Regards,

Lin

[v-lili6-msft]

hi @Anonymous 

You need to use ALL Function in your measure

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/

If not your case, please share your sample data and your expected output.

Regards,

Lin

[Anonymous]

Thanks! I have these tables:

Thanks in advance!

Table1: "Tons"
Production Line	Month	Tons
Line 1	Jan-20	29
Line 2	Jan-20	15
Line 3	Jan-20	25
Line 1	feb-20	28
Line 2	feb-20	30
Line 3	feb-20	13
Line 1	mar-20	14
Line 2	mar-20	10
Line 3	mar-20	19

Table2: "Base"
ID Number	Month
4667	Jan-20
4667	feb-20
4667	mar-20
4843	feb-20
4843	mar-20
8616	Jan-20
8616	feb-20
2224	feb-20
4360	Jan-20
4360	feb-20
4360	mar-20
1312	Jan-20
1312	mar-20
1527	Jan-20
1527	feb-20
1527	mar-20
3288	Jan-20
3288	feb-20
3288	mar-20
3706	feb-20
3094	mar-20
3094	feb-20
3094	mar-20

	Total Tons	Total People	Expected Output
Jan-20	69	6	11,5	= 69/6
feb-20	71	9	7,9	= 71/9
mar-20	43	8	5,4	= 43/8

[parry2k]

@Anonymous As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-calculations/

Connect this table with both of your tables and then use column from this table in your visual and everything will work as expected.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡