Count based on Multi level aggregation

[rajdba2023]

I have a requirement to show a count of dimension which has exceeded the threshold. To make is easier I had put together a sample model to depict the requirement.

This the following Target table has for a month and region combination the target sale amount:

The actual sale table is something like this:

Now when I select a particular month, for instance December 2023, I want a card to show the number of regions which exceeded the sale target. 

How do I achieve this?

[v-jianpeng-msft · Accepted solution]

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.

[rajdba2023]

Thanks for the solution you provided and it does work.

I am new to PowerBI but have exposure to another BI tool and I tried to mimic what I had done in the other tool here in the data model. I have creaed a composite key to join the Sales and Target by combining two keys on both the table as I have to join on Region and Date.

With this datamodel in place I have created a new measure:

Green =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            Sales,
            Region[TargetPeriod],
            Region[Region],
            Sales[SaleMonth],
            Sales[Region],
            "Total Sales", SUM ( Sales[SalesAmount] ),
            "Budget", SUM ( Region[Target] )
        ),
        [Total Sales] > [Budget]
    )
)

This seems to work. I have following questions:

1) Is creating a synthentic key to join two tables when there are more than one key for joining is acceptable solution or should be avioded?

2) Will there be any performance issues you could see on my DAX expression? I need to use this with real world data which runs in to few hundred thousand rows

Sorry that I am unable to attach pbix as I can't find an option to attach to this message!

Thanks,

Raj

[GilbertQ]

Hi @rajdba2023 

You would need to create a measure for the sales amount. And then you would also need to create a measure for the target amout. Once that is done, you will also need a date table linked to both your sales table and your target table . In your date table, you would have a month column if you then had to drag in the region the months from the date table. The sales amount measure and the target amount measure. You would then be able to see the comparisons.

[rajdba2023]

Thanks. I was able to link these two tables. I want to come out a card which will show an aggregated count of regions which has exceeded the target. I am able to print side by table but not sure how to come out with an aggregated overall count.

[GilbertQ]

Hi @rajdba2023 

You will need to create the measures after doing a Star Schema model with the tables and dates with relationships?

[rajdba2023]

That part I was able to achieve by creating a synthetic composite key to join Target table and Sales table.

All I need help is with the DAX on the aggregation.