How can I show monthly target attainment?

[SuperJK5088]

Hi PBI Experts! 

I have three tables - transactions, monthly target and object type (see screenshots below) and I need to determine if the objects are achieving monthly target. How can I do that? I also need to show the cummulative attainment if multiple months are selected in slicer. Category 1 & 2 are category in the second table. Thank you in advance!!!

[Anonymous · Accepted solution]

Thank you Ashish_Mathur and lbendlin 

Hi, @SuperJK5088 

Based on the data you provided, I use the following DAX expression to find the Monthly attainment:

monthly attainment = CALCULATE(
     COUNTROWS('transactions'),
     FILTER('transactions',
     'transactions'[Transaction Date]>=STARTOFMONTH('transactions'[Transaction Date])&&
     'transactions'[Transaction Date]<=ENDOFMONTH('transactions'[Transaction Date])
     )
)

I created a target using the following DAX expression to fetch the target values for the corresponding category and tag:

Target = 
VAR _tag= SELECTEDVALUE(transactions[Tag 1])
VAR _category1 = SELECTEDVALUE(transactions[Category 1])
VAR _target_value = MAXX(FILTER('monthly target','monthly target'[Tag 1]=_tag&&'monthly target'[Category]=_category1),'monthly target'[Target])
RETURN _target_value

 I use the following DAX expression to determine if the current monthly attainment is reaching the target:

Is meet target = IF([monthly attainment]>[Target],"Yes","No")

 The final result is as follows:

When I select multiple months in the slicer, I do a monthly attainment with a rollover:

I've provided the PBIX file used this time below.

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[SuperJK5088]

Hi lbendlin, thank you! Please see sample data attached. 

Object ID	Category 1	Category 2
22362	1	5
22368	2	6
22370	4	8
22385	1	5
22977	2	10
23424	3	10
31271	3	7
31272	3	7

Object ID	Transaction ID	Transaction Date	Tag 1	Category 1	Category 2
22362	11266789	1/5/2024 0:00	2	1	5
22368	11684641	5/3/2024 0:00	3	2	6
22977	11623934	4/17/2024 0:00	1	2	10
22977	12007360	8/6/2024 0:00	2	2	10
31272	11828888	5/31/2024 0:00	2	3	7
22362	11943296	7/14/2024 0:00	2	1	5
22362	11979672	7/27/2024 0:00	2	1	5

frequency	Category	Tag 1	Target
month	1	1	24
month	1	2	16
month	1	3	122
month	2	1	24
month	2	2	16
month	2	3	99
month	3	1	12
month	3	2	4
month	3	3	5

[Ashish_Mathur]

Hi,

Show the expected result very clearly.