use relative filter to calculate delta

[Anonymous]

HI,

I have few tables from which i have created something like this:

	EHS
rows	count	Average	Delta
Hazard id	683	60
AI	15	1.25

for average i am dividing the counts by the month number and the value is coming fine when relative date filter is used

but for the delta i am not getting the correct value. 

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

Is the following image your expected result? I used ALLEXCEPT( 'Table', 'Table'[rows] ) as a filter.

[amitchandak]

@Anonymous , what is delta here. What is the formula of it?

[Anonymous]

for delta i have to do counts divided by average but when i am using this the formula is considering counts from both the row. 

	EHS
rows	count	Average	Delta
Hazard id	683	60	12%
AI	15	1.25	12%

but i want is  percent of 683/60 and 15/1.25 and what it is giving is summing and dividing it equally

[amitchandak]

@Anonymous , unless all or allexcept is used in one of the formula this one should have worked

Divide([count],[Average])

[Anonymous]

Operations Proactive Average = SUM('Operations Proactive'[Count]) / DISTINCTCOUNT('Date Dim'[MonthNumber])

Operations Proactive Delta = DIVIDE(SUM('Operations Proactive'[Count]),[Proactive Average])/100

These are the 2 formulas i am using currently.

[amitchandak]

@Anonymous , this will make it like

(1/ DISTINCTCOUNT('Date Dim'[MonthNumber]))*100

Check if this is what you needed

Operations Proactive Delta = DIVIDE(SUM('Operations Proactive'[Count]),calculate([Proactive Average],all(Table)))/100