Basic calculation help

[chrisingham05]

Hello,

I'm working on a dashboard that requires some calculations and I'm not that familiar with the best way to do it; 

1. Table 1 - List of KPIs
   1. Service Name (unique)
   2. KPI for quantity of service name per FTE
2. Table 2 - List of Config Items and related Service Name
   1. Config Item ID
   2. Customer
   3. Service Name

Report

So far I have a table as follows; 

What I'd like to include is another column with the following formula;

Count of Unique ID (2a) / KPI for quantity of service name per FTE (1b) = FTE 

An example would be 
"ATM All instances L1-L3" of 46 / "KPI for quantity of service name per FTE" of 100 = 0.46 FTE

I'm not sure where to create this formula or how. Any suggestions would help!

[vojtechsima · Accepted solution]

Hi, @chrisingham05 

Measure like this:

FTE_Measure = CALCULATE( DIVIDE ( DISTINCTCOUNT( 'Table 2'[ConfigItemID]), MAX('Table 1'[KPI_FTE])))

Model like this:

[chrisingham05]

Perfect, thanks! 

[vojtechsima]

@chrisingham05 
No, problem, if you could Kudo my Solution answer, so it'S verified in both ways. THank you

[chrisingham05]

So the formula worked as expected one way, but the totals dont seem to work right. Any idea how I can fix this so it just sums the figures above? Total shows 0.19 whereas it should be something like 300. 

[vojtechsima]

Hi, @chrisingham05 
Try modify it like this:

FTE_Measure = IF(ISFILTERED('Your Table'), (CALCULATE( DIVIDE ( DISTINCTCOUNT( 'Table 2'[ConfigItemID]), MAX('Table 1'[KPI_FTE])))), SUM('Table'[ColumnForSum]))​​

Use your own tables and column