Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, I have a trend chart below to show how many hours spent in the month. I would like to see the average hours spent per active products. To calculate the active products, I need to compare the "Date" in TableA with the month in TableB.
The product is considered active if the
For example, in Oct-20, total hours = 11. The difference months between "Date" and Oct-20 for A1 is more than 36 months. Hence the total active products in Oct-20 is 5. The average hours = 11/5 = 2.2 hours.
In Nov-20, total hours = 7. The difference months between "Date" and Nov-20 for A1 and A2 is more than 36 months. Hence the total active products in Oct-20 is 4. The average hours = 7/4 = 1.75 hours.
How to combine the information of these 2 tables to get the expected result?
TableA:
Product | Serial Number | Date |
P-1 | A1 | 05/04/2017 |
P-1 | A2 | 11/13/2017 |
P-2 | A3 | 12/22/2019 |
P-2 | A4 | 02/10/2021 |
P-2 | A5 | 04/19/2021 |
P-1 | A6 | 09/15/2022 |
TableB:
Job Number | Job Date | Product | Labor Hours | Travel Hours | Total Hours |
Job-123 | 10/14/2020 | P-1 | 10 | 1 | 11 |
Job-234 | 11/04/2020 | P-1 | 5 | 2 | 7 |
Job-345 | 01/11/2021 | P-2 | 8 | 4 | 12 |
Job-456 | 03/12/2021 | P-2 | 6 | 2 | 8 |
Job-567 | 04/17/2021 | P-2 | 4 | 5 | 9 |
Job-678 | 05/04/2021 | P-1 | 8 | 1 | 9 |
Solved! Go to Solution.
Hi, @PBI_newuser
You need to add additional fields ‘Job Number ’ into the visual object to make it display correctly.
You can also try the following formulas:
If your expected result of active products is 5, add a new formula as below:
Active products_2.2 =
VAR tab =
SUMMARIZE (
TableB,
TableB[Month],
TableB[Job Number],
"Active products2",TableB[Active products])
return Averagex(tab,[Active products2])
If your expected result of active products is 10(5+5),add a new formula as below:
Active products_2.1 =
VAR tab =
SUMMARIZE (
TableB,
TableB[Month],
TableB[Job Number],
"Active products2",TableB[Active products])
return SUMX(tab,[Active products2])
The calculation of average hours is the same.
Best Regards,
Community Support Team _ Eason
Hi, @PBI_newuser
Can you explain in detail, how did you calculate the total number of active products on October-20? I still don't understand why the total active products is 5.
Best Regards,
Community Support Team _ Eason
Hi @v-easonf-msft , below is an example how to count active products.
In month of Job Date from table B = Oct 2020, you may see only one serial number is more than 36 months, hence it's an expired product. The total active products in Oct 2020 = 5.
In Nov 2020, there are 2 serial numbers expired (more than 36 months), hence the total active products = 4.
Product | Serial Number | Date | YearMonth | Job Month (from TableB) | Months Diff (Oct-2020) | Job Month (from TableB) | Months Diff (Nov-2020) |
P-1 | A1 | 05/04/2017 | 2017-05 | 2020-10 | 42 | 2020-11 | 43 |
P-1 | A2 | 11/13/2017 | 2017-11 | 2020-10 | 36 | 2020-11 | 37 |
P-2 | A3 | 12/22/2019 | 2019-12 | 2020-10 | 11 | 2020-11 | 12 |
P-2 | A4 | 02/10/2021 | 2021-10 | 2020-10 | 13 | 2020-11 | 14 |
P-2 | A5 | 04/19/2021 | 2021-04 | 2020-10 | 7 | 2020-11 | 8 |
P-1 | A6 | 09/15/2022 | 2022-09 | 2020-10 | -24 | 2020-11 | -23 |
Hi, @PBI_newuser
You need to create a summarize tableA to calculate the active product for each date in table B.
Active products =
VAR tab =
SUMMARIZE (
'Table A',
'Table A'[Date],
'Table A'[Serial Number],
"diff_month",
DATEDIFF (
SELECTEDVALUE ( 'Table A'[Date] ),
SELECTEDVALUE ( TableB[Job Date] ),
MONTH
) + 1 //here need add 1 month
)
RETURN
COUNTROWS ( FILTER ( tab, [diff_month] <= 36 ) )
The average hours = SELECTEDVALUE(TableB[Total Hours])/[Active products]
Best Regards,
Community Support Team _ Eason
Hi @v-easonf-msft , thanks for the solution! Usually TableA and TableB has few rows for the same month. For example, I added a new Job Number (Job-444) to TableB and the total hours in 2020-10 is 20 hours. But the active products became 6 instead of 5 and the average hours show empty. Could you please help on this? Thank you so much! 🙂
Sample here.
Hi, @PBI_newuser
You need to add additional fields ‘Job Number ’ into the visual object to make it display correctly.
You can also try the following formulas:
If your expected result of active products is 5, add a new formula as below:
Active products_2.2 =
VAR tab =
SUMMARIZE (
TableB,
TableB[Month],
TableB[Job Number],
"Active products2",TableB[Active products])
return Averagex(tab,[Active products2])
If your expected result of active products is 10(5+5),add a new formula as below:
Active products_2.1 =
VAR tab =
SUMMARIZE (
TableB,
TableB[Month],
TableB[Job Number],
"Active products2",TableB[Active products])
return SUMX(tab,[Active products2])
The calculation of average hours is the same.
Best Regards,
Community Support Team _ Eason
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |