Summarizing 2 tables into 1, using a measure

[aashton]

Hello,

I have two tables of employee data I want to Summarize into one table (I thought this would be the way to break down visuals by employee type).  

TAble 1:  Employee ID, Employee type, Location, FTE (this is a number)

Table 2:  Employee ID, Employee type, Location, and this table does not directly have an FTE, but I created a measure to calculate it.  

Problem is, when I write the SUMMARIZE, it does not let me pick the measure I created for table 2 FTE.  How can I bring this in?

Provider Locations =

UNION(FIlTER(SUMMARIZE('NAPA CR HCM Monthly Data', 'NAPA CR HCM Monthly Data'[Month Year], 'NAPA CR HCM Monthly Data'[Spreadsheet], 'NAPA CR HCM Monthly Data'[Workday Employee ID], 'NAPA CR HCM Monthly Data'[Worker Sub-Type], 'NAPA CR HCM Monthly Data'[Location Identifier], 'NAPA CR HCM Monthly Data'[FTE]), 'NAPA CR HCM Monthly Data'[Month Year]=MAX('NAPA CR HCM Monthly Data'[Month Year]) && 'NAPA CR HCM Monthly Data'[Spreadsheet]="Headcount" && 'NAPA CR HCM Monthly Data'[Worker Sub-Type]="Regular"),FIlTER(SUMMARIZE('Per Diem Pay Slips', 'Per Diem Pay Slips'[Check Date Month Year], 'Per Diem Pay Slips'[Spreadsheet], 'Per Diem Pay Slips'[Employee ID], 'Per Diem Pay Slips'[Worker Sub-Type], 'Per Diem Pay Slips'[Location Identifier], ??), 'Per Diem Pay Slips'[Check Date Month Year]=MAX('Per Diem Pay Slips'[Check Date Month Year])))

[Sahir_Maharaj]

Hello @aashton,

Could you please try:

Provider Locations =
UNION(
    FILTER(
        SUMMARIZE(
            'NAPA CR HCM Monthly Data',
            'NAPA CR HCM Monthly Data'[Month Year],
            'NAPA CR HCM Monthly Data'[Spreadsheet],
            'NAPA CR HCM Monthly Data'[Workday Employee ID],
            'NAPA CR HCM Monthly Data'[Worker Sub-Type],
            'NAPA CR HCM Monthly Data'[Location Identifier],
            'NAPA CR HCM Monthly Data'[FTE]
        ),
        'NAPA CR HCM Monthly Data'[Month Year] = MAX('NAPA CR HCM Monthly Data'[Month Year]) &&
        'NAPA CR HCM Monthly Data'[Spreadsheet] = "Headcount" &&
        'NAPA CR HCM Monthly Data'[Worker Sub-Type] = "Regular"
    ),
    FILTER(
        SUMMARIZE(
            'Per Diem Pay Slips',
            'Per Diem Pay Slips'[Check Date Month Year],
            'Per Diem Pay Slips'[Spreadsheet],
            'Per Diem Pay Slips'[Employee ID],
            'Per Diem Pay Slips'[Worker Sub-Type],
            'Per Diem Pay Slips'[Location Identifier],
            [YourMeasureName]  // Replace [YourMeasureName] with the name of your measure
        ),
        'Per Diem Pay Slips'[Check Date Month Year] = MAX('Per Diem Pay Slips'[Check Date Month Year])
    )
)

Let me know if you might require any further assistance.

[aashton]

No, that doesn't work.  That's the problem, it doesn' let me pick the measure to type it in there.  It only lets me pick columns from that table.

[v-rzhou-msft]

Hi @aashton ,

I think you need to convert your measure to a column in summairze function.

Provider Locations =
UNION (
    FILTER (
        SUMMARIZE (
            'NAPA CR HCM Monthly Data',
            'NAPA CR HCM Monthly Data'[Month Year],
            'NAPA CR HCM Monthly Data'[Spreadsheet],
            'NAPA CR HCM Monthly Data'[Workday Employee ID],
            'NAPA CR HCM Monthly Data'[Worker Sub-Type],
            'NAPA CR HCM Monthly Data'[Location Identifier],
            'NAPA CR HCM Monthly Data'[FTE]
        ),
        'NAPA CR HCM Monthly Data'[Month Year]
            = MAX ( 'NAPA CR HCM Monthly Data'[Month Year] )
            && 'NAPA CR HCM Monthly Data'[Spreadsheet] = "Headcount"
            && 'NAPA CR HCM Monthly Data'[Worker Sub-Type] = "Regular"
    ),
    FILTER (
        SUMMARIZE (
            'Per Diem Pay Slips',
            'Per Diem Pay Slips'[Check Date Month Year],
            'Per Diem Pay Slips'[Spreadsheet],
            'Per Diem Pay Slips'[Employee ID],
            'Per Diem Pay Slips'[Worker Sub-Type],
            'Per Diem Pay Slips'[Location Identifier],
            "Measure Name", [Measure]
        ),
        'Per Diem Pay Slips'[Check Date Month Year]
            = MAX ( 'Per Diem Pay Slips'[Check Date Month Year] )
    )
)

If you use [Measure] couldn't return correct result in new table, then you need to create a new code like a column in [Measure] place. You can try to change max()/min()/sum() to EARLIER() in filter part.

Best Regards,
Rico Zhou

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