cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Unable to calculate Target based on Number of Days selected

Hi,

I am trying to create a formula where I need to calculate the Target based on Number of Days selected.

For example if I have 10 activities to be completed daily and I am looking at 3 days of data then my target would be 30 and if I complete 27 activities then my compliance would be 90%

I think I have got the completion formula correct, its just that something is not right with my Target calculation:

Problem 1:

1. When I select "Dashboard Validation" (1) I want the Target Activities (2) to filter its denominator to only the Dashbaord Validation activities target but it is considering it for all the activities.

Problem 2:

1. When I select 'unselect' everything then my date trend chart shows 100% for all days which is clearly not the case.

Below is the path to the pbix and source file.

https://we.tl/t-rMl69FBwUG

Help is very much appreciated as always!! 🙂

1 ACCEPTED SOLUTION
Frequent Visitor

So my activities per day would always be 47 (Target) as that would not change. What would change is my Completed Activities depending on how many were done that day, so essentially when I divide my 'Completed' by 'Target' that would give me my 'Overall Compliance %'

The 2 issues I have mentioned would be an extention after my above description.

6 REPLIES 6
Frequent Visitor

Okay, so I am able to narrow down the issue.

Now the only problem that remains is that the "Target Activities" is not filtering  as per the context selected.

Right now I have selected Dashbaord Validation and the Target Activities is showing 47 which is the total of all the activities and not only specific to Dashbaord Validation. It should actually be 21 as there are only those many activities for Dashbaord Validation and since its only 1 day. If I select 2 dates then it should be 21 * 2 = 42

Hope I was able to explain properly.

Super User

Can you please not tag 20 people? There are hundreds here that are willing to help and will jump in. It just clutters up notifications. If everyone did this the board would become unusable. Thanks!

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Frequent Visitor

Sure, I will keep that in mind going forward. Thanks for letting me know

Solution Specialist

@mandar5118  - Can you check your Dim table and Governance tables? For example, for 1st AUg 2022 ... the Dim table has 21 activties and Governance table has also 21 activities. Therefore, the complaertion rate is 100%.

Can you point me an example where the completeion%  should not be 100%.

Frequent Visitor

So my activities per day would always be 47 (Target) as that would not change. What would change is my Completed Activities depending on how many were done that day, so essentially when I divide my 'Completed' by 'Target' that would give me my 'Overall Compliance %'

The 2 issues I have mentioned would be an extention after my above description.

Solution Specialist

@mandar5118  - Your DAX calcs are below:

completed = COUNTROWS(Governance)
Target Activities =
COUNT(Dim[Activity])

If for each date in Governance table, you have same no of activities. Then the completeion will be 100%.

If you can provide a clear example of a scenario in your sample .pbix file, where the completed activities should be different from Target activties, that will be great.