Calculating a Percentage of a column

[Alefiyah]

 Hello, 

I'm trying to calculate the percentage of issues in my table that are categorized as CoC. I'm trying to use the filter function to divide the column by itself - count of CoC/total count of Category

So far I have tried using the filter function in different syntaxes but I keep getting errors. I.e. in the syntax below, I get an error saying that 'CoC' table is not found. How do I correct this syntax to achieve the percentage I am looking for?

Syntax: 

Percentage CoC = COUNTX(FILTER('Issues (2)'[Category],[Category]='CoC'))

Data snapshot: 

Thanks!

[Anonymous]

HI

Try this ==> Percentage CoC = COUNTX(FILTER('Issues (2)'[Category],[Category]="CoC"))

[Alefiyah]

Thanks, but I'm still getting this error. 

[Anonymous]

for your percentage use this code:

I used an open table on my pc. Adjust it to your table and fields names

test =

var totalcoc=filter(SUMMARIZE('Goal','Goal'[Name]),'Goal'[Name]="Finn")

return

divide(countrows(totalcoc),countrows('Goal'))

[WinterMist]

@Alefiyah 

I created my own PBIX to calculate the % of Tools in the below test table.

It works using the following 4 Measures:

Hopefully this helps!

Regards,

Nathan

[WinterMist · Accepted solution]

NOTES regarding original code:

1) FILTER is an iterator, and therefore requires RC (Row Context).  As a result, its first argument must be a table, through which it can iterate.  The problem here is that the first parameter in FILTER is not a table.  Add it inside of VALUES function and the error should disappear.

- OLD: 'Issues (2)'[Category] --NOT A TABLE

- NEW: VALUES('Issues (2)'[Category]) --THIS FUNCTION RETURNS A TABLE

2) Additionally, the second parameter of COUNTX (the expression to count) is missing from the code.  See commented code below.

COUNTX(
   FILTER(

      'Issues (2)'[Category],  --FILTER is an iterator....Add VALUES function here

      [Category]="CoC")

   ),
   --EXP TO COUNT IS MISSING HERE
)

Hope this is helpful as well.

Regards,

Nathan

[Alefiyah]

Thanks, 

I'm not sure what I'm dong wrong here, the number I should be getting is 166, but I'm only getting 1.  

[WinterMist]

@Alefiyah -

You're not doing anything wrong.  You have successfully completed the 1st measure (Step 1).

COUNTX is counting 1 for each row WHERE [Category] = "CoC".

If you pull this measure onto a visual table that includes these records, you will find a 1 in the Measure column for all 166 CoC rows.

If you want it to show the TOTAL (in a card visualization) of all these 1's added together to make 166, you need to use a 2nd measure to SUM the results of the 1st measure.

Sum of CoC =
SUMX(           --ITERATE THROUGH TABLE 'Issues 2'

   'Issues 2',

   [Count CoC]  --SUMMING THE RESULTS OF MEASURE [Count CoC] (all the 1's added together)

)

Regards,

Nathan