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 have a dashboard on SSAS model (Direct Query) that is build up on a view of 2 tables. one of the table represents Sales history with a blank forecast date and the other table represents the forecast with multiple forecast dates. I would like to have a comparision of Sales history Vs Forecast for all of the forecast dates.
Data is stored as below in the SSAS Model :
Source | Forecast Date | History | Forecast |
Sales History | 100 | ||
Forecast | 5/26/2018 | 100 | |
Forecast | 6/30/2018 | 50 | |
Forecast | 7/28/2018 | 90 |
The dashboard should look like this. how do show a value for history even though the rows with history doesnt have a date.
Forecast date | Forecast | History | Difference |
5/26/2018 | 100 | 100 | 0 |
6/30/2018 | 50 | 100 | 50 |
7/28/2018 | 90 | 100 | 10 |
Solved! Go to Solution.
You are going to need a measure, that measure will need to make use of the an ALL() function to ignore the filter context.
ALLEXCEPT() may need to be used instead, should there be some amount of filters to be kept. For example you might have a location filter, but you still want data of all time.
You are going to need a measure, that measure will need to make use of the an ALL() function to ignore the filter context.
ALLEXCEPT() may need to be used instead, should there be some amount of filters to be kept. For example you might have a location filter, but you still want data of all time.
I was able to show the data as expected with the allexcept however how can I ignore rowcontext but include filter on slicer. Below is my report. I'm able to show the history which has Blank forecast dates even when the forecast date is not selected. My formula includes filter on start month and product line. Everything works as expected. However I want to use the weighted average of the total history selected in the report. But the report context is coming into picture here. Last column is the sum of the history for the selections made. I want to show the sum as 159405 in all rows. However the sum is shown 27108 for 003 and 132297 for 006. How can I override the row context but make sure the product line filter in the slicer is applied. Below is the formula I have for the measure test
test = CALCULATE(SUM('V_FORECASTACCURACY'[DeliveryQty in SU]), ALLEXCEPT(V_FORECASTACCURACy,V_FORECASTACCURACY[PROFITCENTERBRAND],V_FORECASTACCURACY[STARTMONTH],V_FORECASTACCURACY[STARTWEEk],US_MATERIAL[Product Line]))
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.