Count Distinct percentage group level

[bideveloper555]

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

[v-yiruan-msft · Accepted solution]

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

[v-yiruan-msft · Accepted solution]

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

[bideveloper555]

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

[v-yiruan-msft · Accepted solution]

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

[bideveloper555]

Hi

That's worked beautifully.

Is there way I can define previous sprint, current and future sprint based on start and end dates.

Below dates are as example.

1st of May to 5th May 1st sprint

6th May to 10th May previous sprint

11th May to 15 May current sprint

16th May to 20tj May 4th sprint.

But this will change as dates move on.

Thanks in advance.

I will look into those resource hopefully this week.

[bideveloper555]

Thank you.

Is ther way i can convert SQL statment into DAX.

any resource to learn more about DAX(you would recommend)

[v-yiruan-msft]

Hi @bideveloper555,

There is no direct tool to convert SQL statement to DAX... I would suggest you to review the contents in the following websites for learning DAX:

https://dax.guide/

https://www.sqlbi.com/guides/dax/

https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

Best Regards

Rena