SumIF menor que un número especificado

[chris_rowley]

Hola a todos

Tengo una tabla que contiene datos extraídos de nuestro sistema de registro de tiempo. Después de alguna manipulación tengo estos campos:

• Total_Hours_Per_week
• Hourly_Rate
• Max_hours_Per_Month

Cada miembro del personal podría tener varias filas por semana financiera. Por ejemplo, podría registrar un total de 37,5 horas en 4 proyectos diferentes. otro miembro del personal puede registrar 55,5 horas en 3 proyectos diferentes.

Ejemplo de conjunto de datos

Staff_Name	Semana	Total_Hours_Per_Week	Mes	max_hours_per_month	Hourly_rate
staff1	semana1	10	Abril	187.50	15.35
staff1	semana1	5	Abril	187.50	15.35
staff1	semana1	15	Abril	187.50	15.35
staff1	semana1	7.5	Abril	187.50	15.35
staff2	semana1	15	Abril	187.50	13.25
staff2	semana1	15	Abril	187.50	13.25
staff2	semana1	7.5	Abril	187.50	13.25

La tarifa por hora se explica por sí mismo. las horas máximas por mes son horas totales por semana de 37,5 * número de semanas en un mes. si un mes de 4 semanas horas máximas es 150, si un 5 mes de la semana horas máximas es 187.5

así que esencialmente necesito una medida que realice este cálculo, ya que luego lo pondré en un objeto visual que muestra un desglose del costo por mes por miembro del personal:

si la suma del total de horas > horas máximas por mes

luego max horas * tarifa por hora

suma de horas totales * tarifa por hora

He intentado así diferentes alternativas para obtener las cifras correctas, pero seguir fallando. cualquier ayuda sería muy apreciada.

Gracias

[v-eqin-msft]

Hola @chris_rowley ,

Según mi opinión, usted quiere calcular la tarifa de horas * basado en dos condiciones diferentes, ¿verdad?

Puede seguir estos pasos:

1.Crear una DateTable con fecha , mes , total weekNum por mes columnas como esta:

2. Cree una relación entre dos tablas basada en la columna Mes.

3.Utilice la siguiente fórmula:

Result =
VAR _actualHoursTotal =
    SUM ( 'Table'[Total_Hours_Per_Week] )
        * MAX ( 'DateTable'[Total weekNum per Month] )
RETURN
    IF (
        _actualHoursTotal <= MAX ( 'Table'[max_hours_per_month] ),
        _actualHoursTotal * MAX ( 'Table'[Hourly_rate] ),
        MAX ( 'Table'[max_hours_per_month] ) * MAX ( 'Table'[Hourly_rate] )
)

Mi visualización final tiene este aspecto:

Aquí está el archivo pbix.

¿He respondido a tu pregunta? Por favor, marque mi respuesta como solución. Muchas gracias.

Si no es así, cargue algunas muestras de datos insensibles y la salida esperada.

Saludos

Eyelyn Qin

[chris_rowley]

Desgraciadamente ambas soluciones siguen sin limitar el número total de horas en el maxium permitido para el mes. como se mencionó i tienen una tabla separada para los costos de personal y para los detalles del mes, incluyendo una relación con las tablas también.

He actualizado la fórmula a esto:

Cost_V2 de la clave if(

sum(ABR_DATA[Total_hours_Per_week]) >

min(Calendar_2[Horas de trabajo por mes]),

min(Calendar_2[Horas de trabajo por mes]),

sum(ABR_DATA[Total_hours_Per_week])

)

* min('20/21_Staff_Costs'[Hourly_Staff_Cost])

i simplemente no puedo llegar a las horas por mes con un límite en el máximo permitido para el mes

[P_D_G]

No estoy seguro de entender completamente el problema.

¿Es que la medida sólo devuelve Total_hours_Per_week * Hourly_Staff_Cost como resultado?

¿Podrías cargar en Dropbox/GDrive un archivo .pbix con datos ficticios?

Gracias

Ceo

[chris_rowley]

hola tan incómodo no puedo cargar un archivo debido a las restricciones de tiempo. mal tratar de explicar un poco mejor.

básicamente cada miembro del personal trabaja una cierta cantidad de horas por mes, sin embargo, por razones financieras en el fondo la cantidad de horas se limita al máximo para el mes. básicamente, las finanzas dirán que si un miembro del personal trabaja 200 horas en el mes de abril, entonces un costo por hora sólo se aplica al número máximo de horas para ese mes, es decir, 187.50 horas para abril. las horas extras se manejan espepuladamente.

los datos de la hoja de horas reflejarán el tiempo registrado por miembro del personal por semana por el proyecto en el que han estado trabajando. por lo que independientemente del proyecto puedo obtener el número total de horas trabajadas en este ejemplo 200. i entonces tengo que decir si este total es mayor que el máximo permitido para el mes que es 187.50 entonces necesito para limitar el número total de personal de horas en 187.50 * esto por el costo del personal por hora para darme la cifra total.

Ejemplo:

miembro del personal 1 total de horas trabajó 200 en abril

miembro del personal 2 horas totales trabajaron 185 en abril

horas máximas en abril de 187,50 varía según el mes (p. ej., 150 horas)

tarifa por hora 15.50 - varía según el miembro del personal

fórmula: si 200 > 187.50 entonces 187.50 * 15.50 else si el total de horas < 187.50 entonces total de horas * tarifa por hora.

miembro del personal 1 - costo total 2906.25 (187.50 * 15.50)

miembro del personal 2 - costo total 2867,50 (185 * 15,50)

espero que esto lo explique un poco mejor

[P_D_G · Accepted solution]

Gracias por la expmalación. Esta parte está clara. No estoy seguro de por qué la solución que proporcionamos no está funcionando, qué parte se espera que funcione de manera diferente.

Por favor, eche un vistazo a este archivo PBIX y apunte en la dirección correcta. Esto es lo que tengo:

Gracias

[chris_rowley]

gracias a P_D_G

después de crear algunos nuevos objetos visuales y un par de ajustes con filtros la fórmula ahora funciona correctamente. mis cifras finales todavía están un poco fuera, pero me temo que tal vez algo en mis datos subyacentes que podría ser culpable.

gracias por su ayuda ha sido muy apreciada.

[P_D_G]

hola @chris_rowley ,

la creación de esta medida debería funcionar:

si(

sum('Table'[Total_Hours_Per_Week] ) > min('Table'[max_hours_per_month]),

min('Tabla'[max_hours_per_month]),

sum('Table'[Total_Hours_Per_Week])
)

* min('Tabla'[Hourly_rate])

Sin embargo, reconsideraría la reestructuración de sus datos, por ejemplo, creando tablas separadas con el
1) Month_Name y valores Max_Hours_per_Month
2) Staff_Name &Hourly_rate

Esto podría demostrar el rendimiento del modelo.

Mejor

Ceo

[AllisonKennedy]

¿Tiene una tabla DimDate?

Puedes probar algo como;

Pagar ?
VAR _MaxHrs - MAX(tabla[horas al mes])
devolución
IF( sum(Table[hours]) >_MaxHrs , _MaxHrs* MAX(Table[Hourly Rate]),
sum(Table[hours] *MAX(Table[hourly rate])

¿Cómo ha realizado los cálculos de las tres columnas que ya tiene? Puede haber una manera más eficiente de obtener este resultado que el uso de esos cálculos.