A measure that uses values NOT selected in the slicer.

[CUDataArchitect]

I use a slicer "DatesToAverage" to determine which invoice dates are 'original'. All other invoices are considered an 'offer'. I have a matrix that I want to display only 'offers'. I am having trouble successfully creating a measure that will only show the values of the 'offers' and exclude the 'origional' selected in the slicer. The measure that I want to create is for the "monthly" value in the matrix below. The slicer uses a dataset specifically created for the slicer. There is no relation between the slicer dataset and the dataset used to create the matrix. 

So the only amounts I want to display in the "Monthly" column of the above matrix is the amounts associated with the invoice dates 12/26/17 and 2/7/2018 - the 2 dates not selected in the slicer. Any ideas on how to achieve this?

[TomMartens · Accepted solution]

Hey,

please excuse but i got confused by the Name of the Slicer - I'm sorry for that.

This measure should do the trick:

Monthly = 
CALCULATE(
    SUM('Fact'[Amount])
    ,ALL('Fact'[OfferDate])
    ,EXCEPT(VALUES('Fact'[OfferDate]),VALUES('OfferDates'[Date]))
)

Here is a little screenshot:

The name of the table used in the slicer "Offerdates"

Hopefully this is what you are looking for

Regards

Tom

[TomMartens]

Hey,

I'm wondering how the average should be calculated based on the name of the measure "monthly".

Let's assume there are 2 dates for december 2017 and just one value for february. Let's further assume that the both december values are 1 each and the february value is 10.

Now I can do the following
((1+1)/2 + (10)/1)/2 = 5.5 "calculating the average for each month, and then dividing the sum of the monthly averages by the number of months" dividingor just simply this "calculating
(1+1+10)/3 = 4

Wondering what you would expect

Regards
Tom

[CUDataArchitect]

Hi Tom, 

The "monthly" is a sum of the amounts associated with each invoice date. The dataset is set up something like this:

So the total for "monthly" using only the values that are not selected in the slicer should be $21,500. ( 7,000 for Vendor A, 2,000 for Vendor B and 12,500 for Vendor C). 

All values that are selected in the slicer (9/7/2017, 10/5/2017, 11/14/2017) should be excluded from the "monthly" total used in the matrix. 

[TomMartens · Accepted solution]

Hey,

please excuse but i got confused by the Name of the Slicer - I'm sorry for that.

This measure should do the trick:

Monthly = 
CALCULATE(
    SUM('Fact'[Amount])
    ,ALL('Fact'[OfferDate])
    ,EXCEPT(VALUES('Fact'[OfferDate]),VALUES('OfferDates'[Date]))
)

Here is a little screenshot:

The name of the table used in the slicer "Offerdates"

Hopefully this is what you are looking for

Regards

Tom

[CUDataArchitect · Accepted solution]

Your measure was very helpful! I modified it a little to make it work for my specific setup. My modification required the datasets to have the exact same number of columns so I created 2 new datasets that are identical. I called the first one "PreviousOffers" and "SubsequentOffers". I then set up the measure to look like this:

Monthly = 

SUMX( 

EXCEPT( 'SubsequentOffers', 'PreviousOffers'), 'SubsequentOffers'[MonthlyAmount] )

Thank you very much for your help!