Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mandar5118
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. 

 

 

mandar5118_0-1659889521762.png

 

Problem 2:

 

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

 

 

mandar5118_1-1659889656770.png

 

Below is the path to the pbix and source file.

 

https://we.tl/t-rMl69FBwUG

 

Help is very much appreciated as always!! 🙂

 

 

 

@amitchandak @tamerj1 @SpartaBI @lbendlin @Vijay_A_Verma @Jihwan_Kim @johnt75 @otravers @ray_ux @Ashish_Mathur @Greg_Deckler @PaulDBrown @VahidDM @gauthamboppana @ImkeF @AlexisOlson @ribisht17 @jennratten @edhans @BA_Pete @HotChilli 

1 ACCEPTED SOLUTION

mandar5118_0-1659895948110.png

 

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.

 

Appricaite your response @gauthamboppana 🙂

View solution in original post

6 REPLIES 6
mandar5118
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.

mandar5118_0-1659964484051.png

 

edhans
Super User
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 I answer your question? Mark my post as a solution!
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

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

gauthamboppana
Solution Specialist
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%. 



mandar5118_0-1659895948110.png

 

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.

 

Appricaite your response @gauthamboppana 🙂

@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. 



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.