Power BI DAX formula to calculate difference between 2 columns

[jeesan1234]

I have two tables:
1. Peer Review has columns Team, Reference (which is the policy ID), Banding and it also has other columns but this is the main table
2. Last week's data has columns, Team, Last week's premium I have joined the tables on the column Team.

I want to calculate the difference between count of the References that lie in where Banding is over 30 and last weeks premium in Power BI. 

I have tried the following formula:

Over 30 Mvt. 
IF(
ISBLANK(
CALCULATE(
COUNT('Peer Review'[Reference]),
FILTER('Peer Review', 'Peer Review'[Team_New] = 'Last week data'[Team])
)
),
0,
CALCULATE(((COUNT('Peer Review'[Reference])) - SUM('Last week data'[Last Week Over 30])), FILTER('Peer Review', 'Peer Review'[Banding] = "Over 30 Days"), FILTER('Peer Review', 'Peer Review'[Team_New] = 'Last week data'[Team]))
) 

And I have also tried creating a new measure just to calculate over 30 count reference but all of them are giving me the same results which are not correct:

Team	Count Over 30 Days	Sum of Last Week Over 30	Sum of Over 30 Mvt.
AAW		0
Car	6	12	-6
CT	5	5	0
En	1	1	0
Ex	3	3	0
Heal	5	5	0
In	5	6	-1
IntD	1	1	0
M	42	48	-6
Mari	3	5	0
Mar	1	1	0
O		0
Risk	10	11	-1
PI	1	0	1
P		0
PB		0	1
Sp		0
Fine Art	6	5	7
Te	1	3	-2
Tre	7	7	1
UK		0
US	2	2	0
US2	32	32	0
TOTAL	131	147	-6

[Anonymous · Accepted solution]

Hi Kedar_Pande ,thanks for the quick reply, I'll add more.

Hi @jeesan1234 ,

but all of them are giving me the same results which are not correct

Do you mean the total row is incorrect?

Use the following DAX expression to create measures

Measure = 
VAR _result = 
CALCULATE(
COUNT('Peer Review'[Reference]),
'Peer Review'[Banding] = "Over 30"
)
RETURN
IF(ISBLANK(_result),0,_result)

Measure 2 = 
SUM('Last week data'[Last week's premium])

Measure 3 = 
VAR _table = SUMMARIZE('Last Week data',[Team],"Column1",[Measure],"Column2",[Measure 2],"Column3",ABS([Measure] - [Measure 2]))
RETURN SUMX(_table,[Column3])

If I understand you wrongly, please provide simple data and show the expected results in pictures.

Best Regards,
Wenbin Zhou

[Kedar_Pande]

@jeesan1234 

Create measures:

Count Over 30 = 
CALCULATE(
COUNT('Peer Review'[Reference]),
'Peer Review'[Banding] = "Over 30"
)

Last Week Premium = 
SUM('Last week data'[Last week's premium])

Over 30 Mvt = [Count Over 30] - [Last Week Premium]

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[jeesan1234]

Hi Kedar, thank you for getting back but the solution above gives me the exact same results, I am not sure why.