Forum Discussion
Need help with measure
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:
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
4 Replies
- parry2k
Super User
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.⚡
v-lili6-msftCommunity Support
hi Anonymous
You need to use ALL Function in your measure
If not your case, please share your sample data and your expected output.
Regards,
Lin
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
- v-lili6-msft
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
