Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PARAMGURUS
Frequent Visitor

Ranking by Profit-Difference in Two Months for productCategory Column

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

RankRANKX(  ALL[ProductCategory] , [Profit-Difference]DESC)
 
Here is the sample Data
YearMonth   CategorySubCategory 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-profit2019-Mar-profitProfit DifferenceRank
 Wholesale  372002940078001
 Retail     325002740051002
 Online     3050033000-25003
1 ACCEPTED SOLUTION
xifeng_L
Solution Sage
Solution Sage

Hi @PARAMGURUS 

 

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

 

xifeng_L_0-1716690078716.png

 

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~

View solution in original post

4 REPLIES 4
xifeng_L
Solution Sage
Solution Sage

Hi @PARAMGURUS 

 

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

 

xifeng_L_0-1716690078716.png

 

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~

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
 PARAMGURUS_0-1716713962036.png

 

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.

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.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.