Measure to calculate difference and percent please

[RichOB]

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

[Uzi2019 · Accepted solution]

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!

[Ankur04 · Accepted solution]

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.

[Ankur04 · Accepted solution]

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.

[RichOB]

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

[Anonymous · Accepted solution]

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

[Uzi2019 · Accepted solution]

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!

[Uzi2019]

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!

[RichOB]

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