Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have the following two tables linked to a number of different visuals. Both data sets are dependent on slicers for Date, Product and Retailer.
Sales
Date Client Product Qty Sales
Mar-18 | A | Apples | 2 | $ 2.00 |
Mar-18 | B | Pears | 3 | $ 6.00 |
Mar-18 | C | Apples | 4 | $ 4.00 |
Mar-18 | A | Pears | 3 | $ 6.00 |
Mar-19 | B | Apples | 2 | $ 2.00 |
Mar-19 | B | Pears | 2 | $ 4.00 |
Mar-19 | A | Apples | 2 | $ 2.00 |
Mar-19 | C | Pears | 6 | $ 12.00 |
Mar-19 | A | Apples | 4 | $ 4.00 |
Forecasts
Date Product Retailer Forecast
Mar-19 | Apples | A | $ 15.00 |
Mar-19 | Apples | B | $ 20.00 |
Mar-19 | Apples | C | $ 15.00 |
Mar-19 | Pears | A | $ 30.00 |
Mar-19 | Pears | B | $ 20.00 |
Mar-19 | Pears | C | $ 5.00 |
I would like to create a radial gauge based on the following values:
Maximum value - based on the total forecasts for the month. If I choose a product/retailer, the value must update accordingly. IE If I choose Apples - $50, if I choose Retailer A - $45, if I choose Retailer B & Pears - $20.
Target value - based on the total amount sold last year in the same month IE. What was sold in March 2018. Again, this needs to update accordingly with the slicer for product/retailer/date.
I hope this explains it clearly. Thanks in advance
Peter
Solved! Go to Solution.
Hi @Anonymous
You may create slicer tables and link them as below.Then create a measure.Attached sample file for your reference.
Last Year Sales = CALCULATE ( SUM ( Sales[Sales] ), FILTER ( ALLSELECTED ( Sales ), YEAR ( Sales[Date] ) = YEAR ( MAX ( Forecasts[Date] ) ) - 1 && MONTH ( Sales[Date] ) = MONTH ( MAX ( Forecasts[Date] ) ) ) )
Regards,
Hi @Anonymous
You may create slicer tables and link them as below.Then create a measure.Attached sample file for your reference.
Last Year Sales = CALCULATE ( SUM ( Sales[Sales] ), FILTER ( ALLSELECTED ( Sales ), YEAR ( Sales[Date] ) = YEAR ( MAX ( Forecasts[Date] ) ) - 1 && MONTH ( Sales[Date] ) = MONTH ( MAX ( Forecasts[Date] ) ) ) )
Regards,
Thanks.
1. Maximum value - I forgot to mention that it needs to be date relative as well. I've tried creating a relationship between the dates on my forecast table and the dates on my sales table by it said it can't because its a many-to-many relationship. So I tried creating a distinct date table but it broke my main sales table.
2. Target value - I keep getting a syntax error. It says:
The syntax for ')' is incorrect. (DAX(CALCULATE ( SUM ('Sell In'[Sales]), FILTER ( ALLSELECTED ( 'Sell In'), YEAR ( 'Sell In'[Month.] In ) = YEAR ( MAX ( 'Sell Out'[Date].[Date] ) ) - 1 && MONTH ( 'Sell In'[Month.]) = MONTH ( MAX ( 'Sell Out'[Date].[Date] ) ) )))).
Thanks
Hi @Anonymous
You may format your formula here and it could point out the incorrect syntax ')' .If you need further help,please share your .pbix file or more sample data which could reproduce your scenario and your desired output.You can upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.
Regards,
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |