topic Percentage by Multiple Category in DAX Commands and Tips

Percentage by Multiple Category

ilhamfadhillah4 — Wed, 12 Apr 2023 02:32:45 GMT

HI all, so I've been having this problem of calculating percentages by multiple categories. Here's a sample dataset:

ID	Status	Month	Category
ID1	Complete	Jan	A
ID2	Incomplete	Feb	B
ID3	Complete	Mar	A
ID4	Incomplete	Mar	C
ID5	Complete	Feb	B
ID6	Incomplete	Jan	C

So the ideal result would be to have a monthly percentage of IDs in a category that is complete compared to incomplete. For example if in January there are a total of 10 ID in category A, 8 of which are complete and 2 are incomplete. This would return a percentage of 8/10 or 80%. The visualization I want to show is a clustered chart showing the percentage for each category in each month, kinda like this:

Consider that each color is the category. Any inputs will be welcome, thanks in advance!

Re: Percentage by Multiple Category

FreemanZ — Wed, 12 Apr 2023 03:11:45 GMT

hi ilhamfadhillah4

try to feed a measure like below to the value field of your cluster bar chart:

Measure = DIVIDE( CALCULATE( COUNT(TableName[ID]), TableName[Status]="Complete" ), COUNT(TableName[ID]) )

Re: Percentage by Multiple Category

Jihwan_Kim — Wed, 12 Apr 2023 03:28:01 GMT

Hi,

I am not sure if I undersood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your datamodel.

Expected result measure: = VAR _count = COUNTROWS ( Data ) VAR _completecount = CALCULATE ( COUNTROWS ( Data ), Data[Status] = "Complete" ) RETURN DIVIDE ( _completecount, _count )

Re: Percentage by Multiple Category

ilhamfadhillah4 — Wed, 12 Apr 2023 05:23:12 GMT

Edit: It works! Thank you!

Hi Jihwan,

Tried your solution, but somehow it returns like this

Seems like it returns a 100%. Do you know where it might went wrong? I followed your formula exactly the same.

Re: Percentage by Multiple Category

ilhamfadhillah4 — Wed, 12 Apr 2023 05:23:25 GMT

Edit: It works! Thank you!

Hi Freeman,

Tried your solution, it also returns a 100% like my other reply to Jihwan.

Re: Percentage by Multiple Category

Jihwan_Kim — Wed, 12 Apr 2023 17:52:04 GMT

Hi,

I do not know how your datamodel looks like, and I assume your datamodel looks different than my sample. And I guess your column [DataStatus] is sorted by another column. Try something like the below.

Expected result measure: = VAR _count = COUNTROWS ( Data ) VAR _completecount = CALCULATE ( COUNTROWS ( Data ), FILTER ( ALLSELECTED ( Data ), Data[Status] = "Complete" ) ) RETURN DIVIDE ( _completecount, _count )