Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am trying to do a cross sale. Where whatever value comes in the first column needs to be in reverse orders.
I have only 2 rows.
In the column below for Cross_Sale column I should get :
Row 1 :103
Row2 : 51
Not sure why it is taking the "total" into consideration.
Here is my formula:
Cross_Sales =
VAR RANK_desc =
RANKX (
ALLSELECTED (crop_master[CROP]),
[Increase_SALES_BOTH_CROPS],
,
asc
)
return
CALCULATE (
([INCREASE_SALES_BOTH_CROPS]),
TOPN (
RANK_DESC,
SUMMARIZE (
ALLSELECTED (crop_master[CROP]),
crop_master[CROP],
"TOTAL GOOD", [INCREASE_SALES_BOTH_CROPS]
),
[crop], DESC
)
)
Thanks,
Ritesh
Solved! Go to Solution.
You are seeing the same as the total because that one has a Rank of 2, so your TOPN is keeping the top 2 rows (both in your case). Please see if this expression gets your exprected cross sales result. It will calculate the total sales of all the crops except the one on that row.
Crop Cross Sales =
VAR thiscrop =
VALUES ( crop_master[CROP] )
VAR allcrops =
ALLSELECTED ( crop_master[CROP] )
RETURN
CALCULATE ( [Increase_SALES_BOTH_CROPS], EXCEPT ( allcrops, thiscrop ) )
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@Ritesh_Air see attached, I added an index column in PQ and then measure, in attached, see a table called "Cross"
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
You are seeing the same as the total because that one has a Rank of 2, so your TOPN is keeping the top 2 rows (both in your case). Please see if this expression gets your exprected cross sales result. It will calculate the total sales of all the crops except the one on that row.
Crop Cross Sales =
VAR thiscrop =
VALUES ( crop_master[CROP] )
VAR allcrops =
ALLSELECTED ( crop_master[CROP] )
RETURN
CALCULATE ( [Increase_SALES_BOTH_CROPS], EXCEPT ( allcrops, thiscrop ) )
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
That begs one more question. Could I have done it with the path I was going? Using TopN function, likesomehow remove TOTAL from the equation?
Thanks,
Ritesh
You probably could have. You can calculate the overall total and then subtract the sales for that row to get same result. But the Except route does it simpler.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypat Wow! That was such a simple, yet elegant and powerful solution. Thank you so much!
@parry2k Thanks Parry as well 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |