Reverse a Measure from another measure

[Ritesh_Air]

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

[parry2k]

@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.⚡

[mahoneypat · Accepted 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

[Ritesh_Air]

@mahoneypat 

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]

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

[Ritesh_Air]

@mahoneypat Wow! That was such a simple, yet elegant and powerful solution. Thank you so much!

@parry2k Thanks Parry as well 🙂