cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## FIlter Conect > Measure that calculates delta based on filter

Hi,

I have a question that feels very simple but I just can't work it out.

My data table looks a little like this:

 Country ProductCategory Location Quantity Cost Italy Helmet In Store 6 22 Germany Gloves In Store 8 50 France Gloves In Store 2 21 Spain Shirt In Store 9 25 Italy Helmet Road Show 6 22 Germany Shirt Road Show 9 44 France Helmet Road Show 4 25 Spain Helmet Road Show 2 27 Italy Shirt In Store 7 35 Germany Helmet In Store 6 45 France Helmet In Store 10 41

I've created a measure that calculates the cost per product via CostPerProduct =

```DIVIDE(
SUM(Cost),
SUM(Quantity),
0
)```

What I want to do is create a measure that calculates the difference between the CostPerProduct for "In-Store" and "Road Show" locations. I thought the following measure would help me with this:

CostPerProductDelta =

```CALCULATE(
DIVIDE(
SUM(Cost),
SUM(Quantity),
0
),
Table[Location]="In-Store",
ALL(Table[Location])
) -
CALCULATE(
DIVIDE(
SUM(Cost),
SUM(Quantity),
0
),
ALL(Table[Location])
)```

Now, I would assume htat this would show me the difference between the CostPerProduct for In-Store and Road Show locations. Unfortunately, it sometimes shows a '-Infinity' value for CostPerProductDelta when I set a page level filter to 'In-Store'.

What am I missing here?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: FIlter Conect > Measure that calculates delta based on filter

If you want to calculate the cost per product per country,

```In Store = CALCULATE(SUM(Table1[Cost])/SUM(Table1[Quantity]),FILTER(ALLEXCEPT(Table1,Table1[Country],Table1[ProductCategory]),Table1[Location]="In Store"))

For some line, there is no data for these two measures, to see these, you need to click on any column from the table and enable "show data with no data".

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
Community Support Team

## Re: FIlter Conect > Measure that calculates delta based on filter

If you want to calculate the cost per product per country,

```In Store = CALCULATE(SUM(Table1[Cost])/SUM(Table1[Quantity]),FILTER(ALLEXCEPT(Table1,Table1[Country],Table1[ProductCategory]),Table1[Location]="In Store"))

For some line, there is no data for these two measures, to see these, you need to click on any column from the table and enable "show data with no data".

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regular Visitor

## Re: FIlter Conect > Measure that calculates delta based on filter

Hi Maggie (@v-juanli-msft),

I hope you can find the time to take a look at one other small question regarding your response.

In the future we will probably increase the number of dimensions in the dataset. I don't really want to add all dimensions except 'Location' to the ALLEXCEPT function. Is it safe to assume that the following formula works as well:

`In Store = CALCULATE(SUM(Table1[Cost])/SUM(Table1[Quantity]),FILTER(ALL(Table1[Location]),Table1[Location]="In Store"))`

? It appears to yield the same result now, but I'm not sure if this will still be the case when we add dimensions.