DAX calculation from a table

[jcastr02]

I am trying to do below calculation     

Sum of Terms / Average Headcount as example using 1/1/20+2/1/20 ...

230+231 = 461 (Sum of Terms)

3651+3696 =7,347 / 2 =3,673.5 (average headcount)

461/3,673.5 = 12.55%    but measure shows 12.71% need help with DAX

see screen shots below

[Greg_Deckler]

@jcastr02 Sorry, having trouble following, can you post sample data as text and expected output?

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[jcastr02]

Hello @Greg_Deckler I've provided an example above in screen shot using 1/1/20 +2/1/20

I should have % at 12.55% - Can you take a look at my measure below to see what is incorrect.... trying to do sum of terms / average headcount

[Greg_Deckler]

@jcastr02 - Sorry, but I am not hand typing all of that in order to mock up a model. Maybe someone else will.

[amitchandak]

@jcastr02 ,

Hope you are doing like

divide(sum(Table[Terms]), Average(Table[Headcount]))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[jcastr02]

@amitchandak Hello, please see screen shot of query above, the count type is in a different column ... I also provided an example in the screen shot in lower left side...

[jcastr02]

@amitchandak

In my visual, I'm showing it by month..... Query data provided below - + also screen shot of my visual table...

Attrition should be 12.55% (Sum of Terms / Average Headcount) and looking at this data by month

Query Data
Business Unit	File_date1	Count	Count Type
Miami	1/1/2020	581	Headcount
Chandler	1/1/2020	704	Headcount
Orlando Care	1/1/2020	699	Headcount
Orlando Support	1/1/2020	1081	Headcount
Muscle Shoals	1/1/2020	586	Headcount
Orlando Support	1/1/2020	17	Terms
Miami	1/1/2020	36	Terms
Chandler	1/1/2020	64	Terms
Muscle Shoals	1/1/2020	19	Terms
Orlando Care	1/1/2020	95	Terms
Muscle Shoals	2/1/2020	554	Headcount
Orlando Support	2/1/2020	1100	Headcount
Chandler	2/1/2020	729	Headcount
Miami	2/1/2020	523	Headcount
Orlando Care	2/1/2020	790	Headcount
Chandler	2/1/2020	31	Terms
Orlando Support	2/1/2020	25	Terms
Orlando Care	2/1/2020	109	Terms
Miami	2/1/2020	42	Terms
Muscle Shoals	2/1/2020	23	Terms

visual

[amitchandak]

@jcastr02 , please find attached file after signature

[amitchandak · Accepted solution]

@jcastr02 , please find the attached file after signature

[jcastr02]

@amitchandak I can't tell you how much I appreciate this!!! Thank you

[amitchandak]

@jcastr02 , Try

Divide(sumx(filter(Table, Table[CountType]= "Headcount"),Table[Count]),AverageX(filter(Table, Table[CountType]= "Terms"),Table[Count]))

or

Divide(sumx(filter(Table, Table[CountType]= "Headcount"),Table[Count]),Divide(SumX(filter(Table, Table[CountType]= "Terms"),Table[Count]),Countx(filter(Table, Table[CountType]= "Terms"),Table[Count])))

if this does not work out. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[jcastr02]

@Greg_Deckler Hello Greg, sorry about that, does this help?

Business Unit File_date1 Count Count Type

Miami	1/1/2020	581	Headcount
Chandler	1/1/2020	704	Headcount
Orlando Care	1/1/2020	699	Headcount
Orlando Support	1/1/2020	1081	Headcount
Muscle Shoals	1/1/2020	586	Headcount
Muscle Shoals	2/1/2020	554	Headcount
Orlando Support	2/1/2020	1100	Headcount
Chandler	2/1/2020	729	Headcount
Miami	2/1/2020	523	Headcount
Orlando Care	2/1/2020	790	Headcount
Orlando Support	1/1/2020	17	Terms
Miami	1/1/2020	36	Terms
Chandler	2/1/2020	31	Terms
Orlando Support	2/1/2020	25	Terms
Chandler	1/1/2020	64	Terms
Orlando Care	2/1/2020	109	Terms
Miami	2/1/2020	42	Terms
Muscle Shoals	1/1/2020	19	Terms
Orlando Care	1/1/2020	95	Terms
Muscle Shoals	2/1/2020	23	Terms