# Calculating a filtered measure by an objective one after another measure

• ### Question

• Hi,

in a my workbook I'm trying to calculate a measure major or equals to an objective amount.

So, I'm using the FactFinance table of the AdventureWorksDW2008R2 db and I've created a simple Objectives table with the only Ojective_Amount, mapped creating a linked table.

For now, I haven't created any filters for FactFinance. I've created a sample measure: AmountSum:=sumx(FactFinance; [Amount]).

The goal is to calculate a measure as AmountSum measure over a filtered objective amount.

I've created a slicer using Objective_Amount column and I've connected the slicer to my test pivot table.

I've created this formula:

AmountSumOverObj:=

if(HASONEfilter(Objectives[Objective_Amount]);

calculate( sumx(FactFinance;FactFinance[AmountSum]); filter(FactFinance;FactFinance[AmountSum]>=values(Objectives[Objective_Amount])))

;0)

but it doesn't function. In this way, I filter the Amount of FactFinance major or equals than the filtered objective amount but I want to take into account the AmountSum major or equals to the filtered objective amount.

Any suggests to me, please? Many thanks

Monday, January 28, 2013 10:23 AM

I've AmountSum:=calculate(sum(FactFinance[Amount])) (or AmountSum:=sumx(FactFinance; [Amount])).

I've suited your formula to my case:

```Your_AmountSumOverObj:=IF(HASONEVALUE(Objectives[Objective_Amount]);
SUMX(FILTER(FactFinance; FactFinance[AmountSum]>MIN(Objectives[Objective_Amount])); FactFinance[AmountSum]))```

and I've changed correctly my measure:

`My_AmountSumOverObj:=if(HASONEfilter(Objectives[Objective_Amount]); calculate( sumx(FactFinance;FactFinance[AmountSum]); filter(FactFinance;sumx(factfinance;FactFinance[Amount])>=values(Objectives[Objective_Amount])));0)`

The goal is to before calculate AmountSum and then sum respect to this measure for AmountSum major or equals to an objective amount.

So, for these sample data, My_AmountSumOverObj is always equals to AmountSum.

Thanks

• Marked as answer by Wednesday, January 30, 2013 7:57 AM
Monday, January 28, 2013 10:01 PM

### All replies

Test:=IF(HASONEVALUE(Objectives[Objective_Amount]),
SUMX(FILTER(FactInternetSales, [SalesAmount]>MIN(Objectives[Objective_Amount])), [SalesAmount]))

- www.pmOne.com -

Monday, January 28, 2013 8:27 PM

I've AmountSum:=calculate(sum(FactFinance[Amount])) (or AmountSum:=sumx(FactFinance; [Amount])).

I've suited your formula to my case:

```Your_AmountSumOverObj:=IF(HASONEVALUE(Objectives[Objective_Amount]);
SUMX(FILTER(FactFinance; FactFinance[AmountSum]>MIN(Objectives[Objective_Amount])); FactFinance[AmountSum]))```

and I've changed correctly my measure:

`My_AmountSumOverObj:=if(HASONEfilter(Objectives[Objective_Amount]); calculate( sumx(FactFinance;FactFinance[AmountSum]); filter(FactFinance;sumx(factfinance;FactFinance[Amount])>=values(Objectives[Objective_Amount])));0)`

The goal is to before calculate AmountSum and then sum respect to this measure for AmountSum major or equals to an objective amount.

So, for these sample data, My_AmountSumOverObj is always equals to AmountSum.

Thanks

• Marked as answer by Wednesday, January 30, 2013 7:57 AM
Monday, January 28, 2013 10:01 PM