Forum Discussion

olivere91's avatar
olivere91
Icon for Helper I rankHelper I
4 years ago
Solved

Profit Margin with exception

Hello,

 

I have a matrix, in the matrix, there are columns to show Margin, Margin %. I have a requirement that says if the department code =Blob then take that margin and subtract 21.5% from it. So if the margin was 60% it would show 39.5%,  

 

I can do this writing the following formula, however, I want this step to be skipped for all other department's codes in my matrix. 

 

How can I show this in one column? something along the lines of if department code = Blob use Blob mar, else use GM$.

 

Formula for blob margin: 

Blob mar = calculate(DIVIDE([GM $], Item_Sales_Profit[Total Revenue]),Item_Sales_Profit[Department] = "Blob") -.215
 
Formula for margin column:
 
GM $ = ([Total Sales $]) - [Total Costs]
 
 

 

 

Need Help
  • olivere91's avatar
    olivere91
    4 years ago

    I figured it out using this:

     

    GM Dollars =
    SUMX(VALUES(Item_Sales_Profit[Department]),
    SWITCH(Item_Sales_Profit[Department],
    "BLOB",Item_Sales_Profit[BLOB Margin $],
    [GM $]))

5 Replies

  • olivere91's avatar
    olivere91
    Icon for Helper I rankHelper I
    4 years ago

    I figured it out using this:

     

    GM Dollars =
    SUMX(VALUES(Item_Sales_Profit[Department]),
    SWITCH(Item_Sales_Profit[Department],
    "BLOB",Item_Sales_Profit[BLOB Margin $],
    [GM $]))
  • littlemojopuppy's avatar
    littlemojopuppy
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi olivere91 

     

    Try this...

    VAR	MarginPercentage =
	DIVIDE(
		[GM $], 
		Item_Sales_Profit[Total Revenue],
		BLANK()
	)
RETURN
		
SWITCH(
	Item_Sales_Profit[Department],
	"Blob", MarginPercentage - .215,
	MarginPercentage
)

    FYI: I typed this is Notepad so it might not be syntactically perfect, but it should get you where you want to go.  Not advisable to create calculated columns for everything...this is intended to be a measure.

     

    Hope this helps!

    • olivere91's avatar
      olivere91
      Icon for Helper I rankHelper I
      4 years ago

      Hello, 

       

      Thanks for your reply. This didn't work, the formula returned this error A single value for column 'Department' in table 'Item_Sales_Profit' cannot be determined.

       

      I'm trying to build this as a measure not a column if that's helpful. 

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, olivere91 

     

    Could you please consider sharing more details about it and posting expected result so it is clear on what needs to be implemented?  And It would be great if there is a sample file without any sesentive information here.

     

    It makes it easier to give you a solution.

    1. Sample (dummy dataset) data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    How to provide sample data in the Power BI Forum

    How to Get Your Question Answered Quickly

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.