Forum Discussion

aashton's avatar
aashton
Icon for Helper V rankHelper V
3 years ago

Summarizing 2 tables into 1, using a measure

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, Locati...
Need Help
Sahir_Maharaj's avatar
Sahir_Maharaj
Icon for Super User rankSuper User
3 years ago

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's avatar
aashton
Icon for Helper V rankHelper V
3 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.