Forum Discussion
Helper IVCount Distinct percentage group level
hi
i am working devops data set in power bi.If you are familiar with work items revision table.
i need to show percentage of sub group based on progress of the task.
below is sample data set.
| workitemid | Status | Tagname | iscurrent |
| 1 | Active | sql | yes |
| 1 | Active | sql | no |
| 2 | Completed | ssis | yes |
| 3 | inprogress | ssrs | yes |
| 3 | inprogress | ssrs | no |
| 4 | completed | sql | yes |
| 4 | completed | sql | no |
| 5 | Active | sql | yes |
| 6 | Completed | sql | yes |
| 7 | active | ssis | yes |
after pivot
| current | yes | |||
| sql | ssis | ssrs | total | |
| Active | 2 | 1 | 3 | |
| Completed | 2 | 1 | 3 | |
| inprogress | 1 | 1 |
percentage
| current | yes | |||
| sql | ssis | ssrs | totla | |
| Active | 50.00% | 50.00% | 0.00% | 42.86% |
| Completed | 50.00% | 50.00% | 0.00% | 42.86% |
| inprogress | 0.00% | 0.00% | 100.00% | 14.29% |
What am trying to achive in power bi:
a chart which can display percentage of completed items in tagname
or
showing total no. items per tagname with percentage of completed items.
example: for sql : chart will look like 4 items as total and 50% completed.
thanks
Hi bideveloper555 ,
You can create a measure with below formula to get the completed percentage of per tag:
Completed % of per tag = VAR ccount = CALCULATE ( DISTINCTCOUNT ( 'work items revision'[workitemid] ), FILTER ( 'work items revision', 'work items revision'[Tagname] = MAX ( 'work items revision'[Tagname] ) && 'work items revision'[Status] = "Completed" && 'work items revision'[iscurrent] = "Yes" ) ) VAR fcount = CALCULATE ( DISTINCTCOUNT ( 'work items revision'[workitemid] ), FILTER ( 'work items revision', 'work items revision'[Tagname] = MAX ( 'work items revision'[Tagname] ) ) ) RETURN DIVIDE ( ccount, fcount )Best Regards
Rena
Hi bideveloper555 ,
You can create a calculated column or measure with below formulas:
Measure = IF (TODAY () >= MAX('Sprints'[Startdate]) && TODAY () <= MAX('Sprints'[Enddate]), CONCATENATE ( "Current ", MAX('Sprints'[Sprint]) ), MAX('Sprints'[Sprint]))or
Column = IF ( TODAY () >= 'Sprints'[Startdate] && TODAY () <= 'Sprints'[Enddate], CONCATENATE ( "Current ", 'Sprints'[Sprint] ), 'Sprints'[Sprint] )Best Regards
Rena
6 Replies
Hi bideveloper555 ,
You can create a measure with below formula to get the completed percentage of per tag:
Completed % of per tag = VAR ccount = CALCULATE ( DISTINCTCOUNT ( 'work items revision'[workitemid] ), FILTER ( 'work items revision', 'work items revision'[Tagname] = MAX ( 'work items revision'[Tagname] ) && 'work items revision'[Status] = "Completed" && 'work items revision'[iscurrent] = "Yes" ) ) VAR fcount = CALCULATE ( DISTINCTCOUNT ( 'work items revision'[workitemid] ), FILTER ( 'work items revision', 'work items revision'[Tagname] = MAX ( 'work items revision'[Tagname] ) ) ) RETURN DIVIDE ( ccount, fcount )Best Regards
Rena
bideveloper555Thank you.
i will use this code.
am really sorry to be pain.
can i ask you bit of favour. How to convert SQL statment as below to DAX.
SQL statment : Case when getdate() between startdate and enddate then 'Current'+sprintname else sprintname END.
As i have few sprints (past,current and future) using current date, i need to deferentiate which sprint is current dynamically.
Example:
Sprint1
sprint2
sprint3
As of today sprint 2 is current sprint. but in week time,sprint 3 will be current.
Have a great day 🙂
Thanks.
Hi bideveloper555 ,
You can create a calculated column or measure with below formulas:
Measure = IF (TODAY () >= MAX('Sprints'[Startdate]) && TODAY () <= MAX('Sprints'[Enddate]), CONCATENATE ( "Current ", MAX('Sprints'[Sprint]) ), MAX('Sprints'[Sprint]))or
Column = IF ( TODAY () >= 'Sprints'[Startdate] && TODAY () <= 'Sprints'[Enddate], CONCATENATE ( "Current ", 'Sprints'[Sprint] ), 'Sprints'[Sprint] )Best Regards
Rena