Forum Discussion

lennox25's avatar
lennox25
Icon for Post Patron rankPost Patron
3 years ago
Solved

How to total Categories completed per row

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?

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      lennox25
      Icon for Post Patron rankPost Patron
      3 years ago

      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.

  • nvprasad's avatar
    nvprasad
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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