Answered by:
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
Answers

Hi Gerhard, thanks for your reply.
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 pscorca Wednesday, January 30, 2013 7:57 AM
Monday, January 28, 2013 10:01 PM
All replies

how about this one:
Test:=IF(HASONEVALUE(Objectives[Objective_Amount]),
SUMX(FILTER(FactInternetSales, [SalesAmount]>MIN(Objectives[Objective_Amount])), [SalesAmount])) www.pmOne.com 
Monday, January 28, 2013 8:27 PMAnswerer 
Hi Gerhard, thanks for your reply.
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 pscorca Wednesday, January 30, 2013 7:57 AM
Monday, January 28, 2013 10:01 PM