Forum Discussion

rajdba2023's avatar
rajdba2023
Frequent Visitor
2 years ago
Solved

Count based on Multi level aggregation

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    rajdba2023
    Frequent Visitor
    2 years ago

    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