Calculate the difference between a value of a subcategory

[mandronic]

Hi everyone,

I have the below dataset (sample), where Diff = Actual - Budget:

Currently, I used the below code to calculate the Diff column:

Diff =

var SubCatA = CALCULATE(

SUM('Table'[Value]),

FILTER('Table',

'Table'[Sub Categoty]="Actual" &&

'Table'[Category]="A")

)

var SubCatB= CALCULATE(

SUM('Table'[Value]),

FILTER('Table',

'Table'[Sub Categoty]="Budget" &&

'Table'[Category]="A")

)

return SubCatA-SubCatB

However, with this code I get the below result: 

Can anyone help out with this?

How can I get the Diff result as shown on the first table, for only Category A in the Actual line?

[Pady]

Hope this helps:

diff =

var bud = calculate(SUM(Data[Value]),Data[Sub Category]="Budget")

var diff = IF (SELECTEDVALUE(Data[Sub Category])="Actual",SELECTEDVALUE(Data[Value])-bud,"")

RETURN

diff

[Anonymous]

Hi  @mandronic ,

Here are the steps you can follow：

1. Create calculated column.

If each Category group implements the above logic, you can use the following dax:

Diff 1 =
var Actual=
SUMX(
    FILTER(ALL('Table'),
    'Table'[Category]=EARLIER('Table'[Category])&&'Table'[Sub Category]="Actual"),[Value])
var Budget=
SUMX(
    FILTER(ALL('Table'),
    'Table'[Category]=EARLIER('Table'[Category])&&'Table'[Sub Category]="Budget"),[Value])
return
Actual - Budget

If only Category="A" &&Sub Category="Actual" gives the result, you can use the following dax:

Diff 2 =
var Actual=
SUMX(
    FILTER(ALL('Table'),
    'Table'[Category]=EARLIER('Table'[Category])&&'Table'[Sub Category]="Actual"),[Value])
var Budget=
SUMX(
    FILTER(ALL('Table'),
    'Table'[Category]=EARLIER('Table'[Category])&&'Table'[Sub Category]="Budget"),[Value])
return
IF(
    'Table'[Category]="A"&&'Table'[Sub Category]="Actual",
Actual - Budget,BLANK())

2. Result:

Best Regards,

Liu Yang

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

[Ashish_Mathur · Accepted solution]

Hi,

Your solution will be simpler if you transform this data into a 3 column one - Category, Actual and Budget. using Power Query.

[amitchandak]

@mandronic , Try a measure like

Sumx( Summarize(Table, Table[Category],Table[Sub Category] ) ,
if(Max(Table[Sub Category]) = "Actual",
Calculate(Sum(Table[Value]), filter(allselected(Table), Table[Category] = Max(Table[Categoty]) && Table[Sub Category] = "Budget"))
-Sum(Table[Value]), blank()))

If needed you can use a filter for category A

[mandronic]

Thank you for your reply.

However, using the code you provided, I now get the below (changing the example numbers for better clarity):

However, I would like the result to show (114-153 = -39).

Do you have any ideas how to improve the formula to get this?