Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to Solution.
Hi,
Your solution will be simpler if you transform this data into a 3 column one - Category, Actual and Budget. using Power Query.
Hope this helps:
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
Hi,
Your solution will be simpler if you transform this data into a 3 column one - Category, Actual and Budget. using Power Query.
@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
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?
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |