topic Measure to calculate difference and percent please in DAX Commands and Tips

Measure to calculate difference and percent please

RichOB — Thu, 20 Jun 2024 13:17:49 GMT

Hi, I'm having trouble finding the correct measures that show the # difference and % difference between 2 values.

In this scenario each ID is 1 person and the ID Left is the total of them leaving the company:

Month	ID	ID Left
April	4554	1
April	4354	0
April	4446	0
April	4678	1
May	4112	1
May	4132	1
May	4333	1
May	4589	1
June	4871	0
June	4999	0
June	4989	0
June	4078	1

I need to show it as a table like below:

Month	ID	ID Left	Difference	%
April	4	2	50	50%
May	4	4	100	100%
June	4	1	25	25%

How can I do this, please?

Thanks in advance

Re: Measure to calculate difference and percent please

Uzi2019 — Thu, 20 Jun 2024 13:38:49 GMT

Hi RichOB

Try below dax

ID Count = COUNT(emp[ID])

count of Id Left = CALCULATE(COUNT(emp[ID]),FILTER(emp,emp[ID Left]=1))

Difference = ([count of Id Left]/[ID Count])

I hope i answered your question!

Re: Measure to calculate difference and percent please

Uzi2019 — Thu, 20 Jun 2024 13:39:08 GMT

Hi RichOB

Try below dax

ID Count = COUNT(emp[ID])

count of Id Left = CALCULATE(COUNT(emp[ID]),FILTER(emp,emp[ID Left]=1))

Difference = ([count of Id Left]/[ID Count])

I hope i answered your question!

Re: Measure to calculate difference and percent please

Ankur04 — Thu, 20 Jun 2024 13:51:27 GMT

Hi, you can try created below measures and add them in table.

ID_Count = distinctcount(ID)

ID_left = sum(ID_left)

Difference % = [ID_left] / [ID_Count ] *100

please let me know if this your expectation.

Re: Measure to calculate difference and percent please

RichOB — Thu, 20 Jun 2024 14:22:00 GMT

Hi, thanks for your help!

How would that measure look if the column headers changed to this, please? I tried to replicate but I couldn't get it.

Month	Timeline	Planned
April	4554	1
April	4354	0
April	4446	0
April	4678	1
May	4112	1
May	4132	1
May	4333	1
May	4589	1
June	4871	0
June	4999	0
June	4989	0
June	4078	1

Re: Measure to calculate difference and percent please

RichOB — Thu, 20 Jun 2024 14:53:10 GMT

Hi, Thanks for your help!

How would that look if the column headers changed to the below, please?

Month	Timeline	Planned
April	4554	1
April	4354	0
April	4446	0
April	4678	1
May	4112	1
May	4132	1
May	4333	1
May	4589	1
June	4871	0
June	4999	0
June	4989	0
June	4078	1

Re: Measure to calculate difference and percent please

Anonymous — Mon, 24 Jun 2024 01:47:21 GMT

Hi Uzi2019 , Ankur04 ,Thank you for your quick reply, I will add more.

Hi RichOB ,

Try this.

_Timeline = COUNT('Table'[Timeline])

_Planned = COUNTX(FILTER('Table','Table'[Planned] = 1),[Timeline])

Difference = DIVIDE([_Planned],[_Timeline])

If your column names change, edit here.(Replace 'Table' with your own table name)

Final output