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.
Hi there, Everyone!
So, I have a spreadsheet with sales values as shown below:
[Sales]
Account No. | Sales Date | Sales Year | Sales Amount | Branch | Department | Invoice Number |
AA0012 | 1-Jan-15 | 2015 | $23,043,223.99 | B1 | R&D | 30001 |
AD5013 | 3-Mar-15 | 2015 | $46,378,292.21 | B2 | Personnel | 30002 |
AA0577 | 7-Jul-15 | 2015 | $3,668,282.67 | B1 | R&D | 30003 |
AA0015 | 3-Feb-16 | 2016 | $44,988,324.97 | B4 | R&D | 30004 |
AS7672 | 8-Aug-16 | 2016 | $200,192,927.00 | B3 | Engineering | 30005 |
AQ5452 | 12-Dec-16 | 2016 | $32,434,553.00 | B1 | Personnel | 30006 |
AB6627 | 4-Apr-17 | 2017 | $34,546,411.00 | B1 | Engineering | 30007 |
AD5013 | 11-Aug-17 | 2017 | $465,476,575.00 | B3 | Engineering | 30008 |
Now, to account for inflation over the years, I require a measure (let's call it "SuMm") which can switch between two (2) options from a slicer, i.e. Unadjusted, Adjusted, using data from another table as shown below to get the sum of the "Sales Amount" column either adjusted or unadjusted for corresponding transactions' years:
[Adjustment Rates]
Year | Nominal Rate |
2000 | 4.11 |
2001 | 4.20 |
2002 | 4.43 |
2003 | 4.60 |
2004 | 4.82 |
2005 | 5.00 |
2006 | 5.20 |
2007 | 5.40 |
2008 | 5.60 |
2009 | 5.80 |
2010 | 6.00 |
2011 | 6.20 |
2012 | 6.70 |
2013 | 6.60 |
2014 | 6.80 |
2015 | 7.00 |
2016 | 7.20 |
2017 | 7.40 |
2018 | 7.33 |
2019 | 6.90 |
For example, if choose "Adjusted", the measure would have the following arithmetic breakdown for the example above:
SuMm = ( 23043223.99 / 7.00 )+ ( 46378292.21 / 7.00 ) + ( 3668282.67 / 7.00 ) + ( 44988324.97 / 7.20 ) + ( 200192927 / 7.20 ) + ( 32434553 / 7.20 ) + ( 34546411 / 7.40 ) + ( 465476575 / 7.40 )
If I choose "Unadjusted" instead, SuMm would be the regular sum, i.e. "SUM( 'Sales'[Sales Amount] )".
Could you please help me out? Is such a method possible and if it is, how would you go about creating it?
Thanks in advance!
Solved! Go to Solution.
Hi, Try with this:
1. Create a calculated column in Sales Table:
SalesAmountWAdjustment = DIVIDE ( Sales[Sales Amount], LOOKUPVALUE ( 'Adjustment Rates'[Nominal Rate], 'Adjustment Rates'[Year], Sales[Sales Year] ) )
2. Create a New Table --Using Enter Data
3. Insert a Slicer Visual With Options Field
4. Finally Create a Measure :
Measure = IF ( SELECTEDVALUE ( Options[Options] ) = "Adjusted", SUM ( Sales[SalesAmountWAdjustment] ), SUM ( Sales[Sales Amount] ) )
5. Insert this measure in a Card Visual or another that you desire.
Regards
Victor
Lima - Peru
You can create a parameter hosting the options with datatable, no need for relationship with other tables. Have your measures ready for each option and then use SWITCH and SELECTEDVALUE together and nest your measures inside.
This video explains it in two ways thoroughly:
Hi,
Does anyone have any advice on how to develop a measure that runs from many slicers, with each slicer having more than 10+filters?
I have attached an image where I would like to know the total count based on the customer, location, property type, type of customer, cost category selected in the multiple slicers. Also the option where if nothing is selected on the slicer, then it just counts all cells.
In the solution above, the filter in the slicer is written in the measure. Unfortunately that does not work for me, because in reality I have a load more options than the few in the image, so want to avoid writing these filters in the formula.
Hope this makes sense! Any help will be much appreciated.
Thanks
Hi, Try with this:
1. Create a calculated column in Sales Table:
SalesAmountWAdjustment = DIVIDE ( Sales[Sales Amount], LOOKUPVALUE ( 'Adjustment Rates'[Nominal Rate], 'Adjustment Rates'[Year], Sales[Sales Year] ) )
2. Create a New Table --Using Enter Data
3. Insert a Slicer Visual With Options Field
4. Finally Create a Measure :
Measure = IF ( SELECTEDVALUE ( Options[Options] ) = "Adjusted", SUM ( Sales[SalesAmountWAdjustment] ), SUM ( Sales[Sales Amount] ) )
5. Insert this measure in a Card Visual or another that you desire.
Regards
Victor
Lima - Peru
This totally works for me!
Thank you very much, Victor.
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |