Ranking by Profit-Difference in Two Months for productCategory Column

[PARAMGURUS]

Hi  I need some assistance with a DAX calculation. I'm trying to rank product-categories based on the Profit difference between two specific months.

Here's the approach I've taken:

1. Created Caclulated column  2019-Jan-profit for January 2019 and 2019-Mar-profit for March 2019.
2. Created Calculated column Profit-Difference  =  2019-Jan-profit    -    2019-Mar-profit.
3. Now When I show CAtegory and Profit-Difference it works well.
4. Next I created Rank column of categories based on the sales difference but number our random.

I'm encountering issues with the ranking calculation. The ranking doesn't seem to be accurate, and I suspect there might be an issue with my DAX formulas.

Here are the formulas I've used:

2019-Jan-profit = If(  [YearMonth] = "2019-01", [Profit] , 0.0)

2019-Mar-profit = If(  [YearMonth] = "2019-03", [Profit] , 0.0)

Profit-Difference = 2019-Jan-profit   - 2019-Mar-profit

Rank = RANKX(  ALL( [ProductCategory] ) , [Profit-Difference], , DESC)

Here is the sample Data

YearMonth	Category	SubCategory	Contract	Profit
2019-01	Retail	Brand A	Contract X	5000
2019-01	Wholesale	Brand B	Contract Y	7000
2019-01	Online	Brand C	Contract Z	10000
2019-01	Retail	Brand A	Contract X	8500
2019-01	Wholesale	Brand B	Contract Y	9200
2019-01	Online	Brand C	Contract Z	9500
2019-01	Retail	Brand A	Contract X	9200
2019-01	Wholesale	Brand B	Contract Y	10500
2019-01	Online	Brand C	Contract Z	11000
2019-01	Retail	Brand A	Contract X	9800
2019-01	Wholesale	Brand B	Contract Y	10500
2019-02	Online	Brand C	Contract Z	12000
2019-02	Retail	Brand A	Contract X	7800
2019-02	Wholesale	Brand B	Contract Y	8500
2019-02	Online	Brand C	Contract Z	10500
2019-02	Retail	Brand A	Contract X	9200
2019-02	Wholesale	Brand B	Contract Y	9700
2019-02	Online	Brand C	Contract Z	11500
2019-02	Retail	Brand A	Contract X	9800
2019-02	Wholesale	Brand B	Contract Y	10500
2019-02	Online	Brand C	Contract Z	11000
2019-03	Retail	Brand A	Contract X	8300
2019-03	Wholesale	Brand B	Contract Y	8900
2019-03	Online	Brand C	Contract Z	10500
2019-03	Retail	Brand A	Contract X	9400
2019-03	Wholesale	Brand B	Contract Y	10000
2019-03	Online	Brand C	Contract Z	11500
2019-03	Retail	Brand A	Contract X	9700
2019-03	Wholesale	Brand B	Contract Y	10500
2019-03	Online	Brand C	Contract Z	11000
2019-04	Retail	Brand A	Contract X	8800
2019-04	Wholesale	Brand B	Contract Y	9200
2019-04	Online	Brand C	Contract Z	10500
2019-04	Retail	Brand A	Contract X	9600
2019-04	Wholesale	Brand B	Contract Y	10200
2019-04	Online	Brand C	Contract Z	11700
2019-04	Retail	Brand A	Contract X	9900
2019-04	Wholesale	Brand B	Contract Y	10800
2019-04	Online	Brand C	Contract Z	11200

Expected Output : 

Category	2019-Jan-profit	2019-Mar-profit	Profit Difference	Rank
Wholesale	37200	29400	7800	1
Retail	32500	27400	5100	2
Online	30500	33000	-2500	3

[xifeng_L · Accepted solution]

Hi @PARAMGURUS 

You just need to change all the calculated columns to measures.

Demo - Ranking by Profit-Difference in Two Months for productCategory Column.pbix

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[PARAMGURUS]

Hi @xifeng_L  Well it do works with sample data , but fails with my actual data where I have lot more column and data is quite big.  My rank look like this

 

[xifeng_L]

I can only understand and solve the problem based on the description you gave when you asked the question, and you should try to recreate as much of your actual scenario as possible.

[PARAMGURUS]

Hi @xifeng_L , I realized that there are  two columns with nearly identical names, differing by just two letters. Regrettably, I had been using the incorrect column from the beginning. I appreciate your answer.