Count based on Multi level aggregation

Thanks GilbertQ , Your ideas is great.

Hi, rajdba2023 

Based on your description, I have created the following two tables:

TargetTable:

Sales table:

Based on your description, in my example data, in the slicer selection of January 2024, show 1 region in the card visual that exceeds the target sales, and in the slicer selection December 2023, show 3 regions in the card visual that exceed the target sales.

I created a measure using the following DAX expression:

Result =
VAR _sliceryear =
    SELECTEDVALUE ( TargetTable[TargetPeriod].[Year] )
VAR _slicerMonth =
    MONTH ( SELECTEDVALUE ( TargetTable[TargetPeriod] ) )
VAR _table1 =
    SUMMARIZE (
        ALL ( 'TargetTable' ),
        'TargetTable'[Region],
        'TargetTable'[Target],
        'TargetTable'[TargetPeriod],
        "Year", YEAR ( 'TargetTable'[TargetPeriod] ),
        "Month", MONTH ( 'TargetTable'[TargetPeriod] )
    )
VAR _table2 =
    SUMMARIZE (
        ALL ( 'Sales' ),
        'Sales'[Region],
        Sales[Sales Amont],
        Sales[TargetPeriod],
        "Year1", YEAR ( 'Sales'[TargetPeriod] ),
        "Month1", MONTH ( 'Sales'[TargetPeriod] )
    )
VAR _Table3 =
    FILTER (
        CROSSJOIN ( _table1, _table2 ),
        [Year] = [Year1]
            && [Month] = [Month1]
            && 'Sales'[Region] = 'TargetTable'[Region]
    )
RETURN
    COUNTROWS (
        FILTER (
            _Table3,
            [Year] = _sliceryear
                && [Month] = _slicerMonth
                && 'Sales'[Sales Amont] >= 'TargetTable'[Target]
        )
    )

Create a slicer using the date column in the Target table to keep the year and month:

Here are the results:

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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