problem with a calculation

[Giada90]

Hi, 

I have a table with two fields like this:

type	value
A	10
B	3

I need to make a measure that subtracts value A from value B

Which dax formula can I use?

Thanks

[ddpl · Accepted solution]

@Giada90 try this

Measure 2 = var _A = CALCULATE(SUM('Yourtable'[value]),'Yourtable'[type] = "A")

var _B = CALCULATE(SUM('Yourtable'[value]),'Yourtable'[type] = "B")

return

 (_A - _B)

[Pragati11]

Hi @Giada90 ,

You can create a DAX expression to get this done.

But before that you will need to add an Index column to your data.

I am not sure on how big is your data?

You haven't mentioned if you need to do this subtraction at you type column level or something else? This information is needed to understand whether you have multiple rows for same type.

Please provide atleast 5-10 rows in your sample data, to cover all the scenarios.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Giada90]

@Pragati11 , Hi, the real model is about 1700 rows and I need to do this substraction at my type column level, thanks

[Pragati11]

Hi @Giada90 ,

So can you please provide more number of rows in your sample data please rather than just 2 rows?

[Giada90]

@Pragati11, I need to have the sum of all the values that match with A and substract all the values that match with B, I attached more simple data, thanks 

type	value
A	10
B	3
A	5
B	4
A	7
B	3
A	9
B	2

[Pragati11]

HI @Giada90 ,

The solution that you have accepted, will only work when you have only 2 categories in your data - A and B.

If you are interested in a dynamic solution for your problem, say where you have more number of categories than just A and B, then try the follwing solution I have created.

The sample data I am considering is:

First thing I did was created a Rank column at the type column level  in the data using the following DAX expression:

Rank = 
RANKX(
    TypeTable, 
    TypeTable[total value], , 
    DESC, Dense
    )

When you move this to table visual you will see basically a Rank assigned for every type value in the data.

Now the next thing is we need to get the sum of value column at a Type level.

This we can get using following DAX:

total value at Type level = 
CALCULATE(
    SUM(TypeTable[value]),
    ALLEXCEPT(TypeTable,TypeTable[type])
)

Now we need a difference between these values which should be 31 - 12 = 19 in this case as we just have 2 categories in TYPE column. The following DAX will give the required answer:

Difference at Type level = 
var minRank = CALCULATE(MIN(TypeTable[Rank]), ALL(TypeTable))
var totalValue = CALCULATE(
    SUM(TypeTable[value]),
    ALL(TypeTable),
    TypeTable[Rank] = minRank
)
var nextTotal = 
CALCULATE(
    SUM(TypeTable[value]),
    //ALLEXCEPT(TypeTable, TypeTable[type]),
    ALL(TypeTable),
    TypeTable[Rank] = minRank + 1
)
var diffVal = totalValue - nextTotal
RETURN
diffVal

The result is as follows:

As part of best practice, I always try to use the dynamic solutions as data can change over time.

Hope this solution helps.

[Giada90]

thanks!!

[Pragati11]

HI @Giada90 ,

If you are happy with this solution, please accept this solution to the thread, so that others can benefit from it on the forum.

[Tsrivastava]

DAX=
Var TypeA = calculate(sum(value),filter(tablename, type = A)

Var TypeB = calculate(sum(value),filter(tablename, type = B)
RETURN TypeA-TypeB

[ddpl · Accepted solution]

@Giada90 try this

Measure 2 = var _A = CALCULATE(SUM('Yourtable'[value]),'Yourtable'[type] = "A")

var _B = CALCULATE(SUM('Yourtable'[value]),'Yourtable'[type] = "B")

return

 (_A - _B)

[Giada90 · Accepted solution]

thanks!

[ddpl]

@Giada90 Welcome,

Please accept as solution if its worked.