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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors