Score Card with Dynamic Ranking

Anonymous — Mon, 14 Oct 2024 11:01:50 GMT

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.

Re: Score Card with Dynamic Ranking

Anonymous — Tue, 15 Oct 2024 02:39:18 GMT

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

topic Score Card with Dynamic Ranking in Developer

Score Card with Dynamic Ranking

Re: Score Card with Dynamic Ranking