How to total Categories completed per row

[lennox25]

Hi, 

Can anyone help? 

I need to total the completed categories per row. For example on 31/10/22 There was 5 completed categories (2 blanks). I would like a Total column at the end displaying Total No completed excluding the blanks?

[Anonymous · Accepted solution]

Hi @lennox25 

You can refer to the following two ways

1.Create a calculated column

Column = var a=SUMMARIZE(FILTER('Table',[Cat A]<>BLANK()),[Date],'Table'[Cat A])
var b=SUMMARIZE(FILTER('Table',[Cat B]<>BLANK()),[Date],'Table'[Cat B])
var c=SUMMARIZE(FILTER('Table',[Cat C]<>BLANK()),[Date],'Table'[Cat C])
var d=SUMMARIZE(FILTER('Table',[Cat D]<>BLANK()),[Date],'Table'[Cat D])
var e=SUMMARIZE(FILTER('Table',[Cat E]<>BLANK()),[Date],'Table'[Cat E])
var f=SUMMARIZE(FILTER('Table',[Cat F]<>BLANK()),[Date],'Table'[Cat F])
var g=SUMMARIZE(FILTER('Table',[Cat G]<>BLANK()),[Date],'Table'[Cat G])
return COUNTROWS(FILTER(UNION(a,b,c,d,e,f,g),[Date]=EARLIER('Table'[Date])))

Output

2.Unpivot the table first in power query then create a calculated column

Column = COUNTROWS(FILTER('Table (2)',[Date]=EARLIER('Table (2)'[Date])&&[Value]<>BLANK()))

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lennox25]

Hi @Anonymous, This worked perfectly on my sample report but when tried on my actual real data it is incorrect, this is because there is more than one row with the same date.  Can the 1st calculated columun formula be adjusted to allow this? There will never be the same staff number for the same date.

[lennox25]

Hi @nvprasad  would you be able to advise on the IF formula I could use please? Thank you

[nvprasad]

Hi lennox25,

If you are trying to get this in a new calculated column then If function can get this. The only downside which i could see is if your data is too high (# of rows) then it effects the performance. Also same can be obtained in Power Query Editor using IF logic.

I feel Power Query is the better option compare to the using DAX.

Appreciate a Kudos! ‌‌
If this helps and resolves the issue, please mark it as a Solution! ‌‌

Regards,
VN