Forum Discussion

RvdHeijden's avatar
RvdHeijden
Post Prodigy
9 years ago
Solved

Countif ?

I have a table with all the people that called in sick (a name is not unique in this list and can be in the list more then once) and that is just what i want to know.

I need a column that counts the number of times a name is in the list, preferably in the same table.

 

I want a Stacked Column Chart with on the X-Axis the number of times someone was sick


 

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi RvdHeijden,

    Based on my understanding, you want to get the second example result form the first one, right? If it is, I try to reproduce your scenario and get expected result.



    Create measures using the following formulas.

    count = CALCULATE(COUNTA(Test1[Name]),ALLEXCEPT(Test1,Test1[Name]))

Total sick days = CALCULATE(SUM(Test1[Total SickDays]),ALLEXCEPT(Test1,Test1[Name]))

    Create a table visual, you will get the expected result.



    Please let me know if you have any questions.

    Best Regards,
    Angelia

9 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    9 years ago

    In the Query Editor you can group by name and add aggregation for the other data.

    Next you can expand the nested table excluding the name.

     

     

    Code example:

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Naam", type text}, {"SomeText", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Naam"}, {{"Count", each Table.RowCount(_), type number}, {"AllData", each _, type table}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"SomeText"}, {"SomeText"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded AllData",{"Naam", "SomeText", "Count"})
in
    #"Reordered Columns"
    • RvdHeijden's avatar
      RvdHeijden
      Post Prodigy
      9 years ago

      MarcelBeug

      Im sorry but i dont understand what you are saying, where can i find the Query Editor because when i go to 'Edit Queries' i only have an 'Advanced Editor' and not a 'Query Editor'.

       

      Besides im looking for a formula but in your example is that something i can use in the visuals or just a a matrix

      • MarcelBeug's avatar
        MarcelBeug
        Community Champion
        9 years ago

        With Query Editor I meant Edit Queries.

        Apparently you are looking for a DAX solution.