locked
Calculating a filtered measure by an objective one after another measure RRS feed

  • 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 PM
    Answerer
  • 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