Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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
Solved! Go to Solution.
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 🙂
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.
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!
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSure, I will keep that in mind going forward. Thanks for letting me know
@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%.
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |