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.
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?
Solved! Go to 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
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!
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.
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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |