Medida del DAX que da totales incorrectos

[Syndicate_Admin]

Hola a todos

He creado una medida para calcular los valores proyectados en función de la selección del año para cada empleado.

Medida que he escrito para obtener el Salario Recordatorio Proyectado de la siguiente manera

VAR Max_Period_Year = YEAR(MAX('Payroll Wage Earnings'[Max_Period_in_data]))
VAR Month_End_Year = YEAR(MAX('HEADCOUNT'[MONTH_END_DT]))
VAR Max_Period_Month = MONTH(MAX('Payroll Wage Earnings'[Max_Period_in_data]))
VAR Month_End_Month = MONTH(MAX('HEADCOUNT'[MONTH_END_DT]))

RETURN
CALCULATE(
    ROUND(
        SUMX(
            FILTER(
                'Payroll Wage Earnings',
                Max_Period_Year = (SELECTEDVALUE(MasterTable[Proxy Year]) - 1) &&
                Month_End_Year = (SELECTEDVALUE(MasterTable[Proxy Year]) - 1)
            ),
            IF(
                MAX('HEADCOUNT'[EMPL_STATUS] )<> "A" &&
                MAX('HEADCOUNT'[EMPL_STATUS] ) <> "T",
                [_PayMAXMonth] * (Month_End_Month - Max_Period_Month),
                [_PayMAXMonth]* (12 - Max_Period_Month)
            )
        ),
        0
    )
)

Oye son las tres tablas de datos que tengo.

Tabla de nómina

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tdE9C8IwEAbgvxI6KdTk7nJpGzcXUbCDszgUVBSlQuvivzfFDpmaxg9oyYU2l4d7d7sECQBytEmarKq6PV4OqdhUTev2BMRuQYWqKwXSHMA9YlG+v5puwZxZFrkrYeDtGnhXASrou86Eq3S/EZPyXj/Ot+fU/dSXyT4dw6R/MMlnkiL7NVMHmFCghCKSqX2mDk9zuXVnTda1Wjenqk5FWd0u17GZ69yylYgjlN5N0ZkHlcORf6iMjTyoHE7cKY2RWRap/HniPKy0pFly7CzZV7LS8K3S/ENpfKUJzHL/Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, #"Employee Name" = _t, Year = _t, Period_Start_Date = _t, Proxy_Year = _t, #"001S: Salary Base Pay" = _t, #"009: STD 100%" = _t, #"021: Other Paid Absence" = _t, #"032: Holiday" = _t, #"036: Holiday Personal" = _t, #"040: Vacation" = _t, #"045: Final Vacation Pay" = _t, #"201: Base Salary Expat" = _t, KEY = _t, Period = _t, period_monthvar = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee ID", type text}, {"Employee Name", type text}, {"Year", Int64.Type}, {"Period_Start_Date", type datetime},  {"Proxy_Year", Int64.Type}, {"001S: Salary Base Pay", type number}, {"009: STD 100%", Int64.Type}, {"021: Other Paid Absence", Int64.Type}, {"032: Holiday", Int64.Type}, {"036: Holiday Personal", Int64.Type}, {"040: Vacation", Int64.Type}, {"045: Final Vacation Pay", Int64.Type}, {"201: Base Salary Expat", Int64.Type}, {"KEY", type text}, {"Period", type text}, {"period_monthvar", type text}})
in
    #"Changed Type"

headCount -

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyMDAwN7RU0lHySMwrTs1M0VHwSSwqBvKNDIxMgZShvrGhPpBtomBoZGVgAEQKjr4QaRMk3Y5KsTrEGGekb2RJReOMqes6E31jAyKMCwIb5xYIFDA1MwAKeBalJebpKPgm5mRmK/jmZyTm5iamEBeGSKY4kmgsvrCkwFh8YUqBsfjClgJjTWnjWjPiXAtMCbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EMPLID = _t, NAME = _t, #"Proxy Year" = _t, MONTH_END_DT = _t, KEY = _t, EMPL_STATUS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EMPLID", type text}, {"NAME", type text}, {"Proxy Year", Int64.Type}, {"MONTH_END_DT", type datetime}, {"KEY", type text}, {"EMPL_STATUS", type text}})
in
    #"Changed Type"

masterTable:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyMDAwN7RU0lHySMwrTs1M0VHwSSwqBvKNDIxMkKSBXFOoqFKsTrSSWyBQytTMACjmWZSWmKej4JuYk5mt4JufkZibm5gCVYukDMWEWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, #"Employee Name" = _t, KEY = _t, #"Proxy Year" = _t, #"Sum of Year" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee ID", type text}, {"Employee Name", type text}, {"KEY", type text}, {"Proxy Year", Int64.Type}, {"Sum of Year", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Sum of Year", "Year"}})
in
    #"Renamed Columns"

Modelado de datos: -

Fórmulas DAX:

Pay_MAX_Month = 

Var A ='Payroll Wage Earnings'[001S: Salary Base Pay]
Var B = 'Payroll Wage Earnings'[009: STD 100%]
Var C ='Payroll Wage Earnings'[021: Other Paid Absence]
Var D = 'Payroll Wage Earnings'[032: Holiday]
Var E = 'Payroll Wage Earnings'[036: Holiday Personal]
Var F ='Payroll Wage Earnings'[040: Vacation]
Var G = 'Payroll Wage Earnings'[045: Final Vacation Pay]
Var H = 'Payroll Wage Earnings'[201: Base Salary Expat]

Var totalCount = A+B+C+D+E+F+G+H
VAR PayMaxMonth = 
SWITCH (
    true(),
'Payroll Wage Earnings'[period_monthvar]="Monthly",totalCount,
'Payroll Wage Earnings'[period_monthvar]="Semi-Monthly", totalCount * 2)
VAR MaxPeriodindata = CALCULATE( MAX('Payroll Wage Earnings'[Period_Start_Date]),ALLEXCEPT('Payroll Wage Earnings','Payroll Wage Earnings'[Year],'Payroll Wage Earnings'[Employee ID]))
RETURN

IF('Payroll Wage Earnings'[Period_Start_Date] = MaxPeriodindata,
PayMaxMonth
)

Max_Period_in_data = CALCULATE( MAX('Payroll Wage Earnings'[Period_Start_Date]),ALLEXCEPT('Payroll Wage Earnings','Payroll Wage Earnings'[Year],'Payroll Wage Earnings'[Employee ID]))
//date(max( [Period Start Date])) as Max_Period_in_data

La medida que he escrito me da resultados correctos en cada fila de nivel de empleado, pero los totales no lo son.

Incluso si filtro con la segmentación de estado del empleado, los datos no son correctos.

¿Alguien puede ayudarme a corregir los totales?

Gracias

Mohan V.

[Syndicate_Admin]

Por favor, no copies y pegues tu DAX con errores y esperes que imaginemos lo que quieres. 😀

Por favor, proporcione una descripción clara de lo que está tratando de hacer, con un ejemplo de salida deseada que coincida con los datos de entrada.

¡Que te jodas