Hello,
I have two tables. One table gives detail of Daily and monthly target for each month and category for a user of tasks completed which is given below:
Month/Year | User Name | Daily Target | Monthly Target | Category |
Nov-22 | James | 234 | 2355 | Service |
Nov-22 | John | 567 | 5576 | Asset |
Nov-22 | James | 235 | 6863 | Manufacture |
Nov-22 | John | 676 | 6828 | Service |
The second table is giving the details of each day task completed by each user in their respective category.
I want to count the number of task for each day and month from second table and compare them with first table daily and monthly tasks. I want to get the percentage like if as per 2nd table John completed 48 tasks in Service category so what should be the % achieved for daily similarly for monthly. Second table is below:
Task | User | Date | Time | Category |
AB | James | 11/5/2022 | 12:35:00 PM | Service |
BC | John | 11/5/2022 | 6:39:00 PM | Manufacture |
CD | James | 12/5/2022 | 1:02:00 AM | Asset |
RG | James | 13/5/2022 | 12:00:00 AM | Service |
YI | John | 12/5/2022 | 3:45:00 AM | Service |
WR | James | 11/5/2022 | 10:35:00 PM | Manufacture |
UIO | John | 11/5/2022 | 2:09:00 PM | Manufacture |
Kindly let me knwo should I make data model based on user name?
Please help me understand the DAX function to bemade aling with data model.
Thank you
Solved! Go to Solution.
Hi @Junaid11 ,
According to your description, here's my solution.
Sample:
Target table:
Table:
Note: Month/Year column in Target table is date type, but MonthYear column in Table is text type.
Here's my solution, create two measures:
DailyTargetPercentage =
VAR _Compelete =
COUNTROWS ( 'Table' )
VAR _Target =
MAXX (
FILTER (
'Target',
EOMONTH ( 'Target'[Month/Year], 0 ) = EOMONTH ( MAX ( 'Table'[Date] ), 0 )
&& 'Target'[User Name] = MAX ( 'Table'[User] )
&& 'Target'[Category] = MAX ( 'Table'[Category] )
),
'Target'[Daily Target]
)
RETURN
DIVIDE ( _Compelete, _Target )
MonthlyTargetPercentage =
VAR _Compelete =
COUNTROWS ( 'Table' )
VAR _Target =
MAXX (
FILTER (
'Target',
EOMONTH ( 'Target'[Month/Year], 0 ) = EOMONTH ( MAX ( 'Table'[Date] ), 0 )
&& 'Target'[User Name] = MAX ( 'Table'[User] )
&& 'Target'[Category] = MAX ( 'Table'[Category] )
),
'Target'[Monthly Target]
)
RETURN
DIVIDE ( _Compelete, _Target )
Get the result:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Junaid11 ,
According to your description, here's my solution.
Sample:
Target table:
Table:
Note: Month/Year column in Target table is date type, but MonthYear column in Table is text type.
Here's my solution, create two measures:
DailyTargetPercentage =
VAR _Compelete =
COUNTROWS ( 'Table' )
VAR _Target =
MAXX (
FILTER (
'Target',
EOMONTH ( 'Target'[Month/Year], 0 ) = EOMONTH ( MAX ( 'Table'[Date] ), 0 )
&& 'Target'[User Name] = MAX ( 'Table'[User] )
&& 'Target'[Category] = MAX ( 'Table'[Category] )
),
'Target'[Daily Target]
)
RETURN
DIVIDE ( _Compelete, _Target )
MonthlyTargetPercentage =
VAR _Compelete =
COUNTROWS ( 'Table' )
VAR _Target =
MAXX (
FILTER (
'Target',
EOMONTH ( 'Target'[Month/Year], 0 ) = EOMONTH ( MAX ( 'Table'[Date] ), 0 )
&& 'Target'[User Name] = MAX ( 'Table'[User] )
&& 'Target'[Category] = MAX ( 'Table'[Category] )
),
'Target'[Monthly Target]
)
RETURN
DIVIDE ( _Compelete, _Target )
Get the result:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
User | Count |
---|---|
104 | |
73 | |
69 | |
47 | |
47 |
User | Count |
---|---|
161 | |
85 | |
76 | |
68 | |
67 |