Unable to show Measure Total from Slicer

[Mimi_Ibrahim]

Hello experts,

I have below 2 tables, MasterData and EmployeeData and at the bottom 3 I have the expected results. I managed to filter the data accordingly by lookup the Monthly Details from MasterData using below column.

Monthly Hours =

    LOOKUPVALUE(

        MasterData[Monthly Hours],

MasterData[UID], EmployeeData[UID],  //Country&Team&Year

        MasterData[Start Year].[Year], EmployeeData[Date].[Year],     MasterData[End Year].[Year], EmployeeData[Date].[Year]

    )

However I couldn't get the total displayed in card and also in the table. The total need to be filtered by Team and MonthYear. Can you help me  please? Thank you.

MasterData

Country	Team	Monthly Hours	Start Year	End Year
MY	Team A	148.00	01/01/2022	31/12/2022
TH	Team A	147.50	01/01/2022	31/12/2022
SG	Team A	146.50	01/01/2022	31/12/2022
MY	Team B	148.00	01/01/2022	31/12/2022
UK	Team B	145.00	01/01/2022	31/12/2022
MY	Team A	146.00	01/01/2023	31/12/2023
TH	Team A	145.00	01/01/2023	31/12/2023
SG	Team A	144.00	01/01/2023	31/12/2023
UK	Team B	145.00	01/01/2023	31/12/2023
MY	Team B	146.00	01/01/2023	31/12/2023

EmployeeData

Country	Team	Employee	Monthly Hours	Joined	Exit
MY	Team A	Siti	148.00	01/01/2000	31/12/2022
TH	Team A	Kunthai	147.50	01/01/2000
SG	Team A	Richard	146.50	01/01/2000
MY	Team B	Abu	148.00	01/01/2000	31/05/2022
UK	Team B	Steve	145.00	01/06/2022
UK	Team B	Sarah	145.00	20/06/2022

EXPECTED RESULT - Filter by May 2022

Country	Team	Employee	Monthly Hours	Joined	Exit	Total
MY	Team A	Siti	148.00	01/01/2000	31/12/2022	442.00
TH	Team A	Kunthai	147.50	01/01/2000
SG	Team A	Richard	146.50	01/01/2000

Filter by June 2022

Country	Team	Employee	Monthly Hours	Joined	Exit	Total
MY	Team A	Siti	148.00	01/01/2000	31/12/2022	442.00
TH	Team A	Kunthai	147.50	01/01/2000
SG	Team A	Richard	146.50	01/01/2000
UK	Team B	Steve	145.00	01/06/2022		290.00
UK	Team B	Sarah	145.00	20/06/2022

Filter by Jan 2023

Country	Team	Employee	Monthly Hours	Joined	Exit	Total
TH	Team A	Kunthai	147.50	01/01/2000		294.00
SG	Team A	Richard	146.50	01/01/2000
UK	Team B	Steve	145.00	01/06/2022		290.00
UK	Team B	Sarah	145.00	20/06/2022

[Anonymous · Accepted solution]

Hi @Mimi_Ibrahim 

For your question about not being able to calculate the total, please try the following:

Create a measure. Group by the fields you need and sum. Such as:

Measure = 
    CALCULATE(
        SUM(EmployeeData[Monthly Hours]), 
        FILTER(
            ALL('EmployeeData'), 
            'EmployeeData'[Team] = MAX('EmployeeData'[Team])
        )
    )

Here is the result.

Regards,

Nono Chen

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

[Mimi_Ibrahim]

Many thanks!