based customer performance - provide rank diff b/w two months

[venug20]

Hi Every one,

I have data like below..... 

Aug & Sep month data customer wise sales. When select Sep month in "Slicer"

I want to show employee ranks month wise.....

Sample output like below...

	Sep	Aug
Customer	Rank	Rank
A	4	7
B	2	4
C	3	8
D	6	5
E	1	2
F	7	1
G	8	3
H	5	6

Sept month Data :

Customer	Sep Month	Values	Rank
B	02-09-2018	91526	1
A	09-09-2018	89522	2
E	13-09-2018	88691	3
E	05-09-2018	87961	4
H	16-09-2018	87635	5
F	22-09-2018	84776	6
A	25-09-2018	81789	7
H	08-09-2018	81525	8
D	04-09-2018	78928	9
E	29-09-2018	70720	10
B	10-09-2018	69546	11
C	19-09-2018	68605	12
B	26-09-2018	65927	13
C	27-09-2018	65556	14
C	03-09-2018	65370	15
G	07-09-2018	63438	16
E	21-09-2018	63337	17
A	01-09-2018	57073	18
B	18-09-2018	55731	19
C	11-09-2018	55447	20
D	12-09-2018	48530	21
F	30-09-2018	41918	22
G	23-09-2018	37036	23
F	14-09-2018	33136	24
D	20-09-2018	32201	25
H	24-09-2018	27823	26
G	15-09-2018	23220	27
D	28-09-2018	19360	28
A	17-09-2018	16260	29
F	06-09-2018	12968	30

Aug month Data:

Customer	Aug Month	Values	Rank
A	01-08-2018	58560	27
B	02-08-2018	171762	6
C	03-08-2018	49229	31
D	04-08-2018	148330	10
E	05-08-2018	179588	5
F	06-08-2018	125849	14
G	07-08-2018	102373	20
H	08-08-2018	161610	8
A	09-08-2018	139177	12
B	10-08-2018	94213	22
C	11-08-2018	105722	19
D	12-08-2018	120291	17
E	13-08-2018	102080	21
F	14-08-2018	157071	9
G	15-08-2018	182383	4
H	16-08-2018	188708	1
A	17-08-2018	162381	7
B	18-08-2018	83105	24
C	19-08-2018	55480	28
D	20-08-2018	120415	16
E	21-08-2018	64659	26
F	22-08-2018	83485	23
G	23-08-2018	116187	18
H	24-08-2018	82146	25
A	25-08-2018	50641	30
B	26-08-2018	140643	11
C	27-08-2018	121480	15
D	28-08-2018	53383	29
E	29-08-2018	187093	3
F	30-08-2018	188025	2
G	31-08-2018	132128	13

Thanks in advance....

[PattemManohar]

@venug20 I've tried below steps to solve your scenario...

Use "Group By" option in "Power Query Editor" to group customer wise values and the output will look like below

AugCustSeptCust

Then, add "New Column" using following DAX expression under "Data" pane

AugRnk = RANKX(AugCust,AugCust[Values]) 

SepRnk = RANKX(SepCust,SepCust[Values]) 

Have a relationship between these two tables using "Customer" field and now use a "Table" visual to have a output like this..

Final Output

[venug20]

@PattemManohar

i want to select "Sep" month in Slicer. you can treat as one table for "Aug" & "Sep". 

but should give month selection in "Slicer". based on selection output should be display.

[PattemManohar]

@venug20 I didn't really get what you want with the slicer ? That means, if you select "Sep" in slicer then you want to show Sep and Aug. If you select "Aug" then you want to show Aug and Jul  OR you need to show Aug and Sep (Just order of columns preference)...

Please elaborate a bit....

[venug20]

@PattemManohar

Yes, treat "Aug" & "Sep" as one table (Not Two tables). i want to show "Aug" & "Sep" months in "Slicer".

When i select "Sep", data shows for "Aug" & "Sep" months with rank customer wise.

When i select "Aug" data should be show for "Aug" & "July" months with rank customer wise. (This part, No need now, there is  no data)

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution in this PBI file.

Hope this helps.

 

[venug20]

@Ashish_Mathur

Thanks for provide solution for this query, It's working fine as my point of view. 

Thanks all who are respond to my query.

[Ashish_Mathur]

You are welcome.

[v-frfei-msft]

Hi @venug20,

I made one sample for your reference.

1. Create a calculated table using the formula and create retaltionship between it and teh fact table.

Table = DISTINCT(Table1[Customer])

2. Create the measures as below.

Measure = SUM(Table1[Values])

rankall = RANKX(ALL('Table'),[Measure],,DESC,Dense)

For more details, please check the pbix as attached. Please notice here we cannot meet the requirement as you said: "When i select "Sep", data shows for "Aug" & "Sep" months with rank customer wise." We can select slicer multi options to work around.

Regards,

Frank