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, 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])))

Need Help

3 Replies

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