Percentage difference of two values from one column.

[Anonymous]

I have a matrix with different values in it from the table Acc.

Item	Jan	Feb	Mar
Sales Revenue	443	534	444
Variable Margin	200	243	150
Variable costs	100	34	55
Interest	4	3	3

All the columns belong to a single table. I want to introduce "Variable margin %" = (Sales Revenue - Variable Margin)/Variable margin. 

How can I add "Variable margin %" row in the above matrix?

P.S. I do not wish to add this data in the excel and bring it here.

[ALLUREAN · Accepted solution]

Hi, @Anonymous 

You can do this using a measure and mapping table or custom visual (search for Profitbase, where you can add custom subtotal row for this percent)

You can create a new table like this and use it in matrix (Item column):

Item	Sort
Sales Revenue	1
Variable Margin	2
Variable Margin %	3
Variable costs	4
Interest	5

You need to connect to fact table by account line (Item)

Then you can create a measure:

MeasureName =

VAR vSelection = SELECTEDVALUE(Yourtable[Item])

VAR vDefaultMeasure = [SalesAmount] --or SUM(Sales[Amount]

VAR vRevenue = CALCULATE(SUM(YourTable[Amount]), YourTable[Item] = "Sales Revenue"))

VAR vVariableMargin = CALCULATE(SUM(YourTable[Amount]), YourTable[Item] = "Variable Margin"))

VAR vVariableMarginPercent = DIVIDE(vRevenue - vVariableMargin, vVariableMargin)

-- You can use your measures instead SUM(xxx) if you already created

RETURN

SWITCH(TRUE(),
vSelection = "Variable Margin %", vVariableMarginPercent ,

vDefaultMeasure )

[tamerj1]

Hi @Anonymous 
Can you please present a sample of the expected results?

[Anonymous]

This is my actual data. As you can see, Sales Revenue and Variable costs are part of Line Item. I would like to add another measure which is the differnce of the two divided by Variable costs.

[tamerj1]

@Anonymous Do you mean like this?

Can yu please provide the code of one of your measures?

[Anonymous]

However, this can be used for Business Plan -

Business Plan variance = if(SUM('Combined Data'[Business Plan])<>BLANK(),round(

SUM('Combined Data'[Forecast Value]) - SUM('Combined Data'[Business Plan]),2),blank())

[tamerj1]

@Anonymous 
You can use the row total to display the result. This will require adding the columns as measures only. For example you are adding the Business Plan column and summarizing by sum. You shall remove the column in the visual and instead add the following measure. The same shall apply to all other columns

Business Plan =
VAR BusinessPlan =
    SUM ( 'Combined Data'[Business Plan] )
VAR SalesRevenue =
    CALCULATE (
        SUM ( 'Combined Data'[Business Plan] ),
        'Combined Data'[Item] = "Sales Revenue"
    )
VAR VariableMargin =
    CALCULATE (
        SUM ( 'Combined Data'[Business Plan] ),
        'Combined Data'[Item] = "Variable Marrgin"
    )
VAR VariableCosts =
    CALCULATE (
        SUM ( 'Combined Data'[Business Plan] ),
        'Combined Data'[Item] = "Variable Costs"
    )
RETURN
    IF (
        HASONEVALUE ( 'Combined Data'[Item] ),
        BusinessPlan,
        DIVIDE ( SalesRevenue - VariableMargin, VariableCosts )
    )

[Anonymous]

The measures are values from excel. They are not calculated measures.

[dhruvinushah]

@Anonymous  

$ Calculation = 
var RevSum = SUM('table'[revenue])

var MargSum = SUM('Table'[MargSum'])

var Calcu = ISBLANK(DIVIDE( ( RevSum - MargSum ) , MargSum ),0)

RETURN
Caclu