Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
CUDataArchitect
Frequent Visitor

A measure that uses values NOT selected in the slicer.

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. 

 

Slicer.gifMatrix.gif

 

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?

2 ACCEPTED SOLUTIONS

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:

image.png

 

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

 

Hopefully this is what you are looking for

 

Regards

Tom

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

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!

View solution in original post

4 REPLIES 4
TomMartens
Super User
Super User

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


Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi Tom, 

 

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

 

Dataset.jpg

 

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. 

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:

image.png

 

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

 

Hopefully this is what you are looking for

 

Regards

Tom

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors