Forum Discussion

apenaranda's avatar
apenaranda
Icon for Post Partisan rankPost Partisan
2 years ago

Data from a table (count and sum)

Hello, I have the following table as data

 

NoFechaCod. User
101/01/20241
201/01/20241
303/01/20242
404/01/20243
505/01/20243
601/01/20242
707/01/20244
808/01/20246
908/01/20247
1008/01/20241
1111/01/20241
1212/01/20243
1329/01/20243
1430/01/20244
1531/01/20243

 

I need to get a series of data:
1. For each user, how many registrations are there per day?
Example: User 1 has 4 records
2. For each day, know how many different users have records
Example: for the day 1/1/2024 there are 3 records
For 01/08/2024 there are 3 records
3. From data 2, add the amount
Example: following the previous example, the sum would be 3 + 3 = 6
4. count the days when there are records
Example: according to the previous example, the result would be 2 days.

 

thank you.

General Comment

12 Replies

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    2 years ago

    1) Medida = CALCULATE(COUNTROWS('Tabla'), REMOVEFILTERS('Tabla'[No], 'Tabla'[Fecha]))

    2) Medida2 = CALCULATE(COUNTROWS('Table'), REMOVEFILTERS('Table'[No], 'Table'[Cód. Usuario]))
    3) y 4) son un poco confusos
    • apenaranda's avatar
      apenaranda
      Icon for Post Partisan rankPost Partisan
      2 years ago

      The measure in point 2 makes the sum of all the registrations for that day, but what I want is to know how many different users there are with registrations that day.

       

      Let's explain it in another way to see if it can be understood better:
      Point 3 is basically the sum of records
      Point 4, of the entire month (in this example case it is January), how many days there are with at least 1 record, that is, of the 31 days that January has, how many of them there are any records.

      • Syndicate_Admin's avatar
        Syndicate_Admin
        Icon for Administrator rankAdministrator
        2 years ago

        3) Las filas de conteo simples deberían ser suficientes

        4) Aplique CountRows en la tabla donde la fecha está entre el inicio y el final del mes.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    2 years ago

    Hola @Syndicate_Admin ,

    Gracias @ChiragGarg2512 por sus respuestas y permítanme darles otra idea:


    Para ello, cree una medida.

    Measure =
CALCULATE(COUNT('Table'[No]),FILTER(ALL('Table'),'Table'[Cod. User] = MAX('Table'[Cod. User]))))

    Measure 2 =
CALCULATE(COUNT('Table'[No]),FILTER(ALL('Table'),'Table'[Date] = MAX('Table'[Date]))))

    Measure 3 =
CALCULATE(COUNT('Table'[No]),FILTER(ALL('Table'),'Table'[Measure 2] > 1))

    Measure 4 =
CALCULATE(DISTINCTCOUNT('Table'[Date]),FILTER(ALL('Table'),'Table'[Measure 2] > 1))

    Si su período actual no se refiere a esto, por favor aclárelo en una respuesta de seguimiento.

    Saludos

    Clara Gong

    Si esta publicación ayuda, considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

    • apenaranda's avatar
      apenaranda
      Icon for Post Partisan rankPost Partisan
      2 years ago

      I think we are not understanding each other and I think I also got confused in one of the answers...

       


      Forget option 1 and 2 because I already have them, I attached a screenshot of where you can see them.
      Now I am missing 3 and 4, if you look at the screenshot, in the table below, the total says 6, it is not correct, what I want is the sum that according to the screenshot should be 14 if I have not counted wrong.

      On the other hand, apart from the total sum, I want the count of days, according to the attached table it should be 11 (these are the days with at least 1 record)

      I don't know if the captures have made it better understood.
      Thank you.

      • Syndicate_Admin's avatar
        Syndicate_Admin
        Icon for Administrator rankAdministrator
        2 years ago

        Hola @Syndicate_Admin ,

        Es una situación relativamente común que la medida en el total sea incorrecta, la medida sigue el contexto de la fila "Total" y se calcula en ese contexto. Por lo tanto, las medidas utilizadas en las columnas de visualizaciones de tabla pueden tener valores inesperados en la columna Total.


        Puede utilizar las siguientes medidas, utilizar la función IF() + ISINSCOPE() para determinar el nivel de datos y, a continuación, realizar diferentes cálculos en función del nivel.

        La medida 2 se puede modificar para:

        Measure 2 =
VAR _count = CALCULATE(COUNT('Table'[No]),FILTER(ALL('Table'),'Table'[Date] = MAX('Table'[Date])))
VAR _total_count = COUNT('Table'[No])
RETURN
IF(ISINSCOPE('Table'[Date]),_count,_total_count)

        Si desea calcular el número de días con al menos 1 registro, puede modificar el compás 4 para:

        Measure 4 =
CALCULATE(DISTINCTCOUNT('Table'[Date]),FILTER(ALL('Table'),'Table'[Measure 2] >= 1))

        Si su período actual no se refiere a esto, por favor aclárelo en una respuesta de seguimiento.

        Saludos

        Clara Gong

        Si esta publicación ayuda, considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.