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
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.