Ignore filter/selected value

[SinduN]

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

[Anonymous · Accepted 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.

[SinduN]

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]))