ALLEXCEPT within selection

[Anonymous]

Hi,

My apologies if this question has been put out there before, but I not been able to find one that solves my issue:

I have a dataset where I want to calculate the sum by Table1[Item] within the selection in my slicer. ALLEXCEPT would do the trick, had it not forced the calculation on to the whole data set, ignoring the scope of the slicer.

This formula gives me exactly the answer I want, so long as I dont narrow down the selection with my date slicer (selection = all data):

_AveragePricePerItem = CALCULATE(DIVIDE(SUM(Table1[Sales]);SUM(Table1[VolumeDelivered]));ALLEXCEPT(Table1;Table1[Item]))

If I choose a period in my slicer (Table1[Date]) the measure _AveragePricePerItem stays the same, since ALLEXCEPT ignore the slicer filter. I want the average prices per item to be calculated within the slicer context, by Table1[Item].

This formula respects the slicer filter, but does not group by item.

_AveragePriceAllRows = CALCULATE(DIVIDE(SUM(Table1[Sales]);SUM(Table1[VolumeDelivered]));ALLSELECTED(Table1))

So I guess what I'm after is some sort of hybrid between the two above. Any suggestions as to how I can achieve this?

Please let me know if I need to elaborate further.

Here's a sample of my data:

BR Thomas

[v-lionel-msft]

Hi @Anonymous ,

// if you want a column, do it like this:
_AveragePrice_PerItem_PerDate = 
CALCULATE(
    DIVIDE(
        SUM(Sheet1[Sales]),
        SUM(Sheet1[VD]),
        BLANK()
    ),
    FILTER(
        Sheet1,
        Sheet1[Item] = EARLIER(Sheet1[Item]) && Sheet1[Date] = EARLIER(Sheet1[Date])
    )
)

//if you want a measure, do like this
_AveragePrice = 
CALCULATE(
    DIVIDE(
        SUM(Sheet1[Sales]),
        SUM(Sheet1[VD])
    ),
    ALLSELECTED(Sheet1[Item], Sheet1[Date])
) 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you @v-lionel-msft for your answer. In my case I need this to be a measure.

I realise I haven't been clear enough about the fact that the item is the given by row, not a slicer - sorry about that.

In the example below I have tried to illustrate my expected outcome. The price column is simply the sales divided by VolumeDelivered, simple as that. _AveragePriceAllRows on the other hand is supposed to show the average price on item 1 across all orders within the selected period.

For instance, all sales of item 1 in the selected period, divided by the delivered volume of item 1 in the selected period, regardless of order number or any other row context.

As shown below, the mentioned measure in the opening post gives me the expected result, but my problem is that as I narrow down the period with a slicer on the date, the average prices dont change. I want the average price to change correspondingly to the selected period (in the slicer), but still operating on an item level, not order level.

(This table and its data is just a simplified example to illustrate my point)

[v-lionel-msft]

Hi @Anonymous ,

//Create column
Column 3 = 
CALCULATE(
    DIVIDE(
        SUM([sales]),
        SUM([VD])
    ),
    ALLEXCEPT(
        Sheet14,
        Sheet14[item], Sheet14[Date]
    )
)

//Measure
Measure 3 = 
CALCULATE(
    AVERAGE([Column 3]),
    ALLSELECTED(Sheet14[Date])
)

Best regards,

Lionel Chen