Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jsama
Frequent Visitor

Promedio de asistencias

Hola, tengo una tabla de asistencias con fechas de diferentes meses que incluye el código de cada socios que ingresa, la fecha y la hora, y luce algo así:

jsama_0-1671817474452.png

 

Me interesa conocer las horas pico donde ingresa la mayor cantidad de socios, para ello dividí el día laboral en 3:

jsama_1-1671817559077.png

 

Utilicé el siguiente comando para poder observar cuántos socios ingresan por día en cada uno de los 3 horarios (ojo, un socio puede ingresar más de una vez en un día, o ingresar, salir y volver a ingresar en el mismo momento, por lo que me interesa que el conteo de los socios sea distintivo):

jsama_2-1671817679571.png

 

Y esto es lo que me arroja dicho comando:

jsama_3-1671817721234.png

 

Ahora que tengo esto, estoy interesado en conocer el promedio de ingresos por día, por ejemplo, el jueves ingresaron 3559 socios de 4 am a 8 am, suponiendo que dicho mes hubo 5 jueves, el promedio sería: 3559/5 = 772, pero no sé cómo hacerlo, lo intenté con Averagex pero me da resultados erróneos, o tal vez estoy aplicando mal el comando. Quisiera poder agregar dicha medida a la tabla que está arriba, en el ejemplo dado poder ver:

                           Jueves

                         Total      Promedio
4 am - 8 am      3559           772
¿Alguna idea para poder hacer esto? Desde ya muchas gracias por el apoyo.

6 REPLIES 6
v-rongtiep-msft
Community Support
Community Support

Hi @jsama ,

Please have a try.

Create a calendar table.

table=calendar(date(2020,1,1),date(2022,12,31))

Then create columns.

month=month(table[date])
Year=year(table[date])
weekday=weekday(table[date])
weekday count =
CALCULATE (
    COUNT ( table[weekday] ),
    FILTER (
        table,
        table[month] = EARLIER ( table[month] )
            && table[year] = EARLIER ( table[year] )
            && table[weekday] = EARLIER ( table[weekday] )
    )
)

 

Then create relationships between the tables.

Then create a measure.

measure=[total Ingresos]/max(table[weekday count])

 

How to Get Your Question Answered Quickly 

 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

 

Best Regards
Community Support Team _ Polly

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

I just have a final problem, I did what you told me, and it helped me a lot, but when I use the measure

measure=[total Ingresos]/max(table[weekday count])

 

It always gives me the max number of the month, and thats not always right, let me give you an example:

In August 2022 there where 4 sundays, so when I divide the total of access it should be: 307/4 = 76.7, but the Max number of the column "weekday count" its 5, it divides the total of access by 5. If there were 4 sundays I need to divide the total of access by 4.

jsama_0-1672850524357.png

jsama_1-1672850620494.png

 

Hi @jsama ,

Please have a try.

Change the weekday column.

weekday=weekday(table[date],2)
weekday count =
CALCULATE (
    COUNT ( table[weekday] ),
    FILTER (
        table,
        table[month] = EARLIER ( table[month] )
            && table[year] = EARLIER ( table[year] )
            && table[weekday] = 7
    )
)

 

Best Regards
Community Support Team _ Polly

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

AlB
Super User
Super User

@jsama 

Is the matrix visual you are showing  being filterd for August in one specific year? If so, it should work. 

Can you share the pbix?

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

AlB
Super User
Super User

Hi @jsama 

You already have the "Total" so you only need to count the number of "jueves".  What field are you using for the day? A COUNT of that field should suffice. Something like 

COUNT(DateT[WeekDay])

Then you can divide the total by this value

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

 

jsama
Frequent Visitor

I created a column for the field day using:

Day = format(Date, "dddd")

 

I tried with 

count(Date[Day])

 

But it gives me the total amount of rows that I have, so when I divide the total by this new value it shows me this: 

jsama_0-1671826448312.png

This is the total access of an specific day divided by the total of access.

In the example that I gave, I need the total access of thursday divided by the total of thursday in a period of time. For example, in august there were 4 thursdays, and the total access in august was 3559, so its going to be 3559/4 =  890

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors