Percentage by Multiple Category

[ilhamfadhillah4]

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!

[Jihwan_Kim]

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 )

[ilhamfadhillah4]

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.

[Jihwan_Kim]

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 )

[FreemanZ]

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])
)

[ilhamfadhillah4]

Edit: It works! Thank you!

Hi Freeman,

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