Forum Discussion

Ritesh_Air's avatar
Ritesh_Air
Icon for Post Patron rankPost Patron
6 years ago
Solved

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's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft 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's avatar
      Ritesh_Air
      Icon for Post Patron rankPost Patron

      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's avatar
        mahoneypat
        Icon for Microsoft Employee rankMicrosoft 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

         

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