Ranking by Profit-Difference in Two Months for productCategory Column

PARAMGURUS — Sat, 25 May 2024 16:58:03 GMT

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:

Created Caclulated column 2019-Jan-profit for January 2019 and 2019-Mar-profit for March 2019.
Created Calculated column Profit-Difference = 2019-Jan-profit - 2019-Mar-profit.
Now When I show CAtegory and Profit-Difference it works well.
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

Re: Ranking by Profit-Difference in Two Months for productCategory Column

xifeng_L — Sun, 26 May 2024 02:23:33 GMT

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~

Re: Ranking by Profit-Difference in Two Months for productCategory Column

PARAMGURUS — Sun, 26 May 2024 09:00:22 GMT

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

Re: Ranking by Profit-Difference in Two Months for productCategory Column

xifeng_L — Sun, 26 May 2024 13:16:02 GMT

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.

Re: Ranking by Profit-Difference in Two Months for productCategory Column

PARAMGURUS — Sun, 26 May 2024 16:40:56 GMT

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.

topic Re: Ranking by Profit-Difference in Two Months for productCategory Column in DAX Commands and Tips

Ranking by Profit-Difference in Two Months for productCategory Column

Re: Ranking by Profit-Difference in Two Months for productCategory Column

Re: Ranking by Profit-Difference in Two Months for productCategory Column

Re: Ranking by Profit-Difference in Two Months for productCategory Column

Re: Ranking by Profit-Difference in Two Months for productCategory Column