Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

ALLEXCEPT within selection

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[It...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
v-lionel-msft
Community Support
6 years ago

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