Re: Greatest difference

Mema · ‎10-28-2019

I have a table that has 3 columns that make up a unique identifier, I have combined these in a new column. I have been able to do the difference for each one of these for each period in the financial year but I want to show which of these combinations had the greatest change each financial period and which period had the most movement.

Thanks for any help

Anonymous · ‎10-28-2019

Hi @Mema

I'm not sure what the question is here.

Can you advised what you have tried and what you are having a problem with?

Mema · ‎10-28-2019

I am asking how I can show the top 5 ranked differences, I have tried using Rankx in a calculated column, then using top n as a filter. I have tried to create a rankx measure, I can't get that to work at all, I have tried a filter on the measure that is rank less than or equal to 5

dax · ‎10-28-2019

Hi Mema,

You said you can't make the rankx work in report, which problem did you get? And what is rankx difference? In addition, if possible, could you please inform me more detailed information (such as your sample data and your expected output)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

Mema · ‎10-29-2019

Sample of my Data

Period	ManageCodeKey	ActivityCodeKey	ItemCodeKey	OutturnForecast	Outturn Dif	OutturnDif Rank	ManageActivityItem
3	27634	222249	22432	-1,229.09	0.00	126828	2763422224922432
4	27634	222249	22432	-1,229.09	0.00	126828	2763422224922432
5	27634	222249	22432	-1,229.09	0.00	126828	2763422224922432
6	27634	222249	22432	-1,229.09	0.00	126828	2763422224922432
7	27634	222249	22432	-3,415.59	2,186.50	44012	2763422224922432
8	27634	222249	22432	-4,644.68	1,229.09	55946	2763422224922432
9	27634	222249	22432	-4,644.68	0.00	126828	2763422224922432
10	27634	222249	22432	-4,644.68	0.00	126828	2763422224922432
11	27634	222249	22432	-4,644.68	0.00	126828	2763422224922432
12	27634	222249	22432	-4,644.68	0.00	126828	2763422224922432
4	27639	213456	21775	402.52	0.00	126828	2763921345621775
5	27639	213456	21775	465.93	63.41	111299	2763921345621775
6	27639	213456	21775	465.93	0.00	126828	2763921345621775
7	27639	213456	21775	473.00	7.07	124063	2763921345621775
8	27639	213456	21775	788.32	315.32	85495	2763921345621775
9	27639	213456	21775	788.32	0.00	126828	2763921345621775
10	27639	213456	21775	788.32	0.00	126828	2763921345621775
11	27639	213456	21775	1,799.11	1,010.79	60094	2763921345621775
12	27639	213456	21775	1,799.11	0.00	126828	2763921345621775
4	27639	213456	22107	191.39	0.00	126828	2763921345622107
5	27639	213456	22107	222.78	31.39	117661	2763921345622107
6	27639	213456	22107	10,263.52	10,040.74	18382	2763921345622107
7	27639	213456	22107	13,869.78	3,606.26	33861	2763921345622107
8	27639	213456	22107	13,869.78	0.00	126828	2763921345622107
9	27639	213456	22107	15,791.73	1,921.95	46694	2763921345622107
10	27639	213456	22107	16,148.40	356.67	83117	2763921345622107
11	27639	213456	22107	16,620.47	472.07	77115	2763921345622107
12	27639	213456	22107	16,970.47	350.00	83489	2763921345622107
3	27639	213456	22528	-5,779.01	0.00	126828	2763921345622528
4	27639	213456	22528	-5,779.01	0.00	126828	2763921345622528
5	27639	213456	22528	-5,779.01	0.00	126828	2763921345622528
6	27639	213456	22528	-5,779.01	0.00	126828	2763921345622528
7	27639	213456	22528	3,207.99	8,987.00	19685	2763921345622528
8	27639	213456	22528	3,207.99	0.00	126828	2763921345622528
9	27639	213456	22528	4,457.99	1,250.00	55564	2763921345622528
10	27639	213456	22528	4,457.99	0.00	126828	2763921345622528
11	27639	213456	22528	25,986.97	21,528.98	11787	2763921345622528
12	27639	213456	22528	45,765.99	19,779.02	12433	2763921345622528

The rank that I can get to work is the one over the whole table

OutturnDif Rank =

RANKX(

CombinedData,

CombinedData[Outturn Dif],

,

DESC,

Dense

)

I would like to be able to show is which activity item combination has the biggest difference for each ManageCode or which ones have had the biggest difference for a certain period.

Thanks for your help, I hope this helps make sense of what I am trying to achieve

dax · ‎10-30-2019

Hi Mema,

If you want to rank based on ManageCode, you could try to use below calculated column

OutturnDif Rank1 =
RANKX (
    FILTER (
        CombinedData,
        CombinedData[ManageCodeKey] = EARLIER ( CombinedData[ManageCodeKey] )
    ),
    CombinedData[Outturn Dif],
    ,
    ASC,
    DENSE
)

Best Regards,
Zoe Zhi

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

Mema · ‎10-30-2019

you for your response, I get the error EARLIER/EARLIEST refers to an earlier row context which doesn't exist

dax · ‎10-30-2019

Hi Mema,

Above expression is used on calculated column, not in measure.

Best Regards,
Zoe Zhi

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

Mema · ‎10-30-2019

I have tried it as a column and I get

A Single value for column 'ManageCodeKey' in the table 'CombinedData' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result