Sum of Values in same table but with filters

[jcastr02]

I have a table with a count column (value) and a count type column   (either Headcount or Terms).

How can I get a measure to divide the       sum of headcount   /    sum of terms          

[Greg_Deckler]

@jcastr02 - So, should be something like:

Measure =
  DIVIDE(
    SUMX(FILTER(ALL('Table'),[Type]="Headcount"),[Count]),
    SUMX(FILTER(ALL('Table'),[Type]="Terms"),[Count]),
    0
  )

[jcastr02]

apologies @Greg_Deckler        Meant to say         Sum of Terms    /     AVERAGE of Headcount        -- flipped around. 

I tried to do AverageX in it's place but didn't work...

[Greg_Deckler]

@jcastr02 - Not sure why this wouldn't work

Measure =
  DIVIDE(
    SUMX(FILTER(ALL('Table'),[Type]="Terms"),[Count]),
    AVERAGEX(FILTER(ALL('Table'),[Type]="Headcount"),[Count]),
    0
  )

[jcastr02]

@Greg_Deckler Hello, It seems that the measure stays static, so when I click on example 9/1/2019 I should see % being 252/3798 = 6.63%, but no matter what date I click on, the number stays the same for the measure created.

[Greg_Deckler]

@jcastr02 - Use ALLSELECTED instead of ALL

[jcastr02]

@Greg_Deckler    Still seems to do incorrect calculation - as example for 11/1/2019 & 12/1/2019....... 

Sum of Terms =   309+265=574

Average of Headcount = 7,511 / 2   = 3,755.5

Attrition =  574 / 3,755.5 = 15.28%

[jcastr02]

@Greg_Deckler   Any ideas on how I can achieve this?  I appreciate your help.

[Greg_Deckler]

@jcastr02 - Post sample data as text and expected output and I will take a look

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]

Hi @Greg_Deckler   Thanks for your help - I added some sample data in red box in image

[jcastr02]

Hello @Greg_Deckler I added some sample data above, any luck with that one?  Thanks so much and appreciate the help. 

[v-xuding-msft]

Hi @jcastr02 ，

Please try this:

Measure =
VAR terms =
    CALCULATE ( SUM ( 'Table'[Count] ), 'Table'[Count Type] = "Terms" )
VAR Headcount =
    AVERAGEX (
        ALL ( 'Table'[File_date1] ),
        CALCULATE ( SUM ( 'Table'[Count] ), 'Table'[Count Type] = "Headcount" )
    )
RETURN
    DIVIDE ( terms, Headcount )

[jcastr02]

Thanks so so much @v-xuding-msft    It looks like I'm close - when I do my measure I get 12.71%, but should be 12.55% - Are you able to double check me?  Thanks again for help..

[jcastr02]

@v-xuding-msft   any help would be appreciated on above..thanks in advance