How to create a table by using DAX for jan month top 5 each channel top 5 spgrs by taking abs value

[tkavitha911]

input data 

 Out put i want to display like this 

[tkavitha911]

first i want to sort top 5 by focusing the abs value of january month, and second requirement will be that top 5 spgrs i want to dispaly for comparision perpose sep 2023 to jan 2024. like this by using matrix table 

[talespin]

hi @tkavitha911 

I want to display data for the month of January - What is the logic, current month/previous month

which month?

[tkavitha911]

I want to display data for the month of January, focusing on absolute values, and the M-3 Accuracy(Nivea

percentage from September 2023 to January 2024. While I can currently display data for January alone, I specifically want to view only the absolute values for that month. 

[talespin]

hi @tkavitha911 

You can use a measure to RANK and filter data from Filter pane, but there must be some logic to make it dynamic(which month data you want to see?)

Rank =

 RANK(DENSE, ALLSELECTED('Rank'), ORDERBY('Rank'[abs], DESC), PARTITIONBY('Rank'[CHANNEL]))

[Anonymous]

Hi @tkavitha911 ,

You can create a computing table to get the top five ABS among each CHANNEL.

Top5spgr =
Var RANKEDSPGR = Addcolumns (
'Table',
"RANK", RANKX (Filter (all ('table'), 'table' [channel] = Earlier ('table' [chaannel])), [abs], desc, dense)
Cure
Return
SelectColumns (
Filter (
RANKEDSPGR,
[Rank] <= 5
),,
"Channel", [Channel],
"Spgr", [spgr realinged],
"Period", [Period],
"Accuracy", [M-3 account (nivea)],],
"ABS", [ABS]
)

But I don't quite understand the percentage of the SPGR you said. It refers to who compares and who, which helps me better help you solve the problem.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[tkavitha911]

Heare i want to show top 5 for jamuary month 

[tkavitha911]

heare i want to display january 2024 month for each channel top 5 spgr's by using the abs value heare no need to display accuracy nivea. (that is just for adding the values for different months in a visual)  

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[tkavitha911]

CHANNEL	SPGR Realinged	Period	M-3 Accuracy(Nivea)	abs
GT	80101	01-11-2023	0.3302	420380
GT	89059	01-11-2023	0.782095	292452
MT	89054	01-11-2023	0.587362	275705
MT	80204	01-11-2023	0.591634	266189
MT	80102	01-11-2023	0.467382	251847
GT	85281	01-12-2023	0	189464
MT	80204	01-12-2023	0.482066	177962.7
GT	89053-11	01-11-2023	0	175798
GT	89059	01-12-2023	0.308602	163544
GT	85281	01-11-2023	0.468827	140501
GT	80205	01-12-2023	0.662918	137774
GT	88835	01-01-2024	0	137513.7
MT	80101	01-11-2023	0.435667	132573
MT	81600	01-12-2023	0.104	130830.3
GT	85282	01-12-2023	0	130336
GT	89054	01-01-2024	0.547036	126425.4
MT	80204	01-01-2024	0	123852
GT	80201	01-11-2023	0.80208	123661
GT	80205	01-01-2024	0.269625	119651.4
GT	80204	01-11-2023	0.517204	116502
GT	89053	01-11-2023	0.776534	115515
GT	83840	01-11-2023	0.63675	114645
MT	80102	01-12-2023	0.416209	97904.11
GT	89054	01-12-2023	0.853225	93179
MT	89050	01-11-2023	0.53458	87989
GT	88408	01-11-2023	0.611332	86263
GT	81600	01-01-2024	0.308632	84340
GT	80201	01-01-2024	0	84168.07
GT	82252	01-01-2024	0	83918.05
GT	89050	01-11-2023	0.686211	81200
GT	89053	01-12-2023	0.627215	80151
MT	85077	01-11-2023	0.301145	79984
GT	80204	01-01-2024	0	76600.52
MT	85781	01-12-2023	0.520189	72678.93
GT	83734	01-12-2023	0.424544	69743
MT	82808	01-01-2024	0	69461.55
GT	80205	01-11-2023	0.962314	68829
GT	80101	01-12-2023	0.724009	67971
GT	87414	01-11-2023	0.5814	65994
MT	88476	01-11-2023	0.78422	65920
MT	88409	01-11-2023	0.437607	65363
GT	89050	01-01-2024	0	63715.01
GT	83731	01-01-2024	0.391011	62983.44
GT	82808	01-01-2024	0	62538.59
GT	80140	01-11-2023	0.284502	59767
GT	89053-11	01-12-2023	0	59538
GT	87400	01-01-2024	0	57841
GT	80305	01-11-2023	0.64954	57813
GT	81601	01-01-2024	0.339555	57573
GT	88868	01-01-2024	0	56768.93

[tkavitha911]

this is my sample data i want to write a dax for table creating for each channel top 5 SPGR's based on abs value then i want to display for each spgr accuracy percentage pls help me guys