Forum Discussion
Reverse a Measure from another measure
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
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
5 Replies
- mahoneypat
Microsoft Employee
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
- Ritesh_Air
Post Patron
mahoneypat Wow! That was such a simple, yet elegant and powerful solution. Thank you so much!
parry2k Thanks Parry as well 🙂
- Ritesh_Air
Post Patron
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
- mahoneypat
Microsoft Employee
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
- parry2k
Super User
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.⚡