Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Score Card with Dynamic Ranking

Hi Everyone.

I have an issue with score card and column charts where i need the dynamic ranking.
I have a category column with 16 distinct values in it and few other columns which im using as filters. Lets say 5 filters im using.
I need to Show the Category name in one scorecard and its percentage value in another score card and its last 6 months cloumn trend to its side.
Similarly i need to keep for 16 category values.
I need the dynamic ranking for it. 
I tried creating the dynamic ranking in measure. but it was not working. im getting all the values as "1"
I need to display all 16 rows  of two scorecard and a chart, in accending order of percentage value. that why i need a ranking. Based on the slicer selections the rank should change.
Lets say if i select one slicer value and it has onlt 5 categories, only i need to show the top 5 rows and remaining rows of score cards and graph should be empty. 
Im able to crack this by creating static ranking by creating a calculated table.But thats not the requriment.
Kindly help me with some ideas.
Thanks it advance.

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi  Anonymous ,

    I created some data:

     

    Here are the steps you can follow：

    1. Create measure.

    category Name =
VAR _group1 =
    SELECTEDVALUE ( 'Table'[Group1] )
VAR _group2 =
    SELECTCOLUMNS ( 'Table', "test", 'Table'[Group2] )
VAR _table =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Group1] = _group1
            && 'Table'[Group2] IN _group2
    )
VAR _table2 =
    SUMMARIZE ( _table, [Category] )
RETURN
    CONCATENATEX ( _table2, [Category], "-" )

    category percentage =
VAR _group1 =
    SELECTEDVALUE ( 'Table'[Group1] )
VAR _group2 =
    SELECTCOLUMNS ( 'Table', "test", 'Table'[Group2] )
VAR _today =
    TODAY ()
VAR _last6month =
    DATE ( YEAR ( _today ), MONTH ( _today ) - 6, DAY ( _today ) )
VAR _table =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Group1] = _group1
            && 'Table'[Group2] IN _group2
    )
VAR _table2 =
    ADDCOLUMNS (
        _table,
        "test1",
            [Category] & "-"
                & ROUND (
                    SUMX (
                        FILTER (
                            _table,
                            [Category] = EARLIER ( [Category] )
                                && [Group1] = EARLIER ( [Group1] )
                                && [Group2] = EARLIER ( [Group2] )
                                && [Date] >= _last6month
                                && [Date] <= _today
                        ),
                        [Value]
                    )
                        / SUMX (
                            FILTER (
                                _table,
                                [Category] = EARLIER ( [Category] )
                                    && [Group1] = EARLIER ( [Group1] )
                            ),
                            [Value]
                        ),
                    2
                )
    )
VAR _table3 =
    SUMMARIZE ( _table2, [Category], [test1] )
RETURN
    CONCATENATEX (
        FILTER ( _table3, [Category] <= MAX ( [Category] ) ),
        [test1],
        "/"
    )

    category rank =
VAR _group1 =
    SELECTEDVALUE ( 'Table'[Group1] )
VAR _group2 =
    SELECTCOLUMNS ( 'Table', "test", 'Table'[Group2] )
VAR _today =
    TODAY ()
VAR _last6month =
    DATE ( YEAR ( _today ), MONTH ( _today ) - 6, DAY ( _today ) )
VAR _table =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Group1] = _group1
            && 'Table'[Group2] IN _group2
    )
VAR _table2 =
    ADDCOLUMNS (
        _table,
        "test1",
            ROUND (
                SUMX (
                    FILTER (
                        _table,
                        [Category] = EARLIER ( [Category] )
                            && [Group1] = EARLIER ( [Group1] )
                            && [Group2] = EARLIER ( [Group2] )
                            && [Date] >= _last6month
                            && [Date] <= _today
                    ),
                    [Value]
                )
                    / SUMX (
                        FILTER (
                            _table,
                            [Category] = EARLIER ( [Category] )
                                && [Group1] = EARLIER ( [Group1] )
                        ),
                        [Value]
                    ),
                2
            )
    )
VAR _table3 =
    ADDCOLUMNS ( _table2, "rank", RANKX ( _table2, [test1],, DESC, DENSE ) )
RETURN
    MAXX ( FILTER ( _table3, [Category] = MAX ( 'Table'[Category] ) ), [rank] )

    2. Result:

     

     

    Best Regards,

    Liu Yang

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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi  Anonymous ,

    I created some data:

     

    Here are the steps you can follow：

    1. Create measure.

    category Name =
VAR _group1 =
    SELECTEDVALUE ( 'Table'[Group1] )
VAR _group2 =
    SELECTCOLUMNS ( 'Table', "test", 'Table'[Group2] )
VAR _table =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Group1] = _group1
            && 'Table'[Group2] IN _group2
    )
VAR _table2 =
    SUMMARIZE ( _table, [Category] )
RETURN
    CONCATENATEX ( _table2, [Category], "-" )

    category percentage =
VAR _group1 =
    SELECTEDVALUE ( 'Table'[Group1] )
VAR _group2 =
    SELECTCOLUMNS ( 'Table', "test", 'Table'[Group2] )
VAR _today =
    TODAY ()
VAR _last6month =
    DATE ( YEAR ( _today ), MONTH ( _today ) - 6, DAY ( _today ) )
VAR _table =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Group1] = _group1
            && 'Table'[Group2] IN _group2
    )
VAR _table2 =
    ADDCOLUMNS (
        _table,
        "test1",
            [Category] & "-"
                & ROUND (
                    SUMX (
                        FILTER (
                            _table,
                            [Category] = EARLIER ( [Category] )
                                && [Group1] = EARLIER ( [Group1] )
                                && [Group2] = EARLIER ( [Group2] )
                                && [Date] >= _last6month
                                && [Date] <= _today
                        ),
                        [Value]
                    )
                        / SUMX (
                            FILTER (
                                _table,
                                [Category] = EARLIER ( [Category] )
                                    && [Group1] = EARLIER ( [Group1] )
                            ),
                            [Value]
                        ),
                    2
                )
    )
VAR _table3 =
    SUMMARIZE ( _table2, [Category], [test1] )
RETURN
    CONCATENATEX (
        FILTER ( _table3, [Category] <= MAX ( [Category] ) ),
        [test1],
        "/"
    )

    category rank =
VAR _group1 =
    SELECTEDVALUE ( 'Table'[Group1] )
VAR _group2 =
    SELECTCOLUMNS ( 'Table', "test", 'Table'[Group2] )
VAR _today =
    TODAY ()
VAR _last6month =
    DATE ( YEAR ( _today ), MONTH ( _today ) - 6, DAY ( _today ) )
VAR _table =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Group1] = _group1
            && 'Table'[Group2] IN _group2
    )
VAR _table2 =
    ADDCOLUMNS (
        _table,
        "test1",
            ROUND (
                SUMX (
                    FILTER (
                        _table,
                        [Category] = EARLIER ( [Category] )
                            && [Group1] = EARLIER ( [Group1] )
                            && [Group2] = EARLIER ( [Group2] )
                            && [Date] >= _last6month
                            && [Date] <= _today
                    ),
                    [Value]
                )
                    / SUMX (
                        FILTER (
                            _table,
                            [Category] = EARLIER ( [Category] )
                                && [Group1] = EARLIER ( [Group1] )
                        ),
                        [Value]
                    ),
                2
            )
    )
VAR _table3 =
    ADDCOLUMNS ( _table2, "rank", RANKX ( _table2, [test1],, DESC, DENSE ) )
RETURN
    MAXX ( FILTER ( _table3, [Category] = MAX ( 'Table'[Category] ) ), [rank] )

    2. Result:

     

     

    Best Regards,

    Liu Yang

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