Measure to ignore columns

[kaspars_b]

Hi!

I have a table with measure (from fact table) and 4 attributes from 4 different dimenion tables joined to this fact.

I have used Calculate + filter + all formula in the measure to get the necesasary filter context (e.g. all dates smaller then the one in slicer).

Next I'm trying to write a formula that witout modifying this filter context would let me sum by two of the dimensions, disregarding other two. And only for those dates that the first measure has filtered.

So for instance I have DATE / PRODUCT / STORE /  EMPLOYEE / AMOUNT and I'm trying to write a formula for AMOUNTBYPRODUCTSTORE that would show on each row with PRODUCT + STORE combination the total for that combination in the table without impacting filter cotext on DATE given by first AMOUNT formula and disregarding EMPLOYEE column

Is this possible? Please suggest the approach? Thanks

[lbendlin]

I need some clarification terminology

without impact

disregarding

those terms can have multiple meanings.

I assume you are familiar with KEEPFILTERS and ALLEXCEPT concepts.  Can you please describe for each of your scenarios which filters you want to enforce, which filters you want to keep, and which filters you want to remove?

[kaspars_b]

Hi, 

I'll try to explain again:

As a starting point I have a table with four dimensions and a measure 

DATE / PRODUCT / STORE / EMPLOYEE / AMOUNT 

In the Amount column I have a Calculate + filter + all formula for filtering all dates <= max (date). This then gives me all history in the table until the the DATE I chose in the slicer.

Let's say AMOUNT can also be negative - e.g. due to returns and return can have different EMPLOYEE and different DATE then the sale. So the end goal is to filter out sales and their returns in case on STORE and PRODUCT basis the sum is 0 - but for that I need a measure first.

So I'm trying to come up with formula to do what is done below:

The new measure should within the table sum by PRODUCT and STORE  and then display the sum on each row.

DATE / PRODUCT / STORE / EMPLOYEE / AMOUNT   /  NEW MEASURE

1      /   BAG        /       1     / JOHN          / 100              /  0

2     /   PILLOW   /       2     / JOHN          / 100              / 150

3     /   BAG       /       1      / ANNA         / -100             / 0

4    /   BALL       /        2     /  JOHN         / 200              / 200

5    /   PILLOW   /       2      / ANNA        /  50                / 150

Is this clearer now? Thanks A LOT