cancel
Showing results 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

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 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
1 ACCEPTED SOLUTION
Solution Sage

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~

4 REPLIES 4
Solution Sage

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~

Frequent Visitor
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

Solution Sage

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.

Frequent Visitor

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.

Announcements

#### 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 Monthly Update - June 2024

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

#### 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.

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors