Ignore filter in measure

[Hrtfkr]

Hey folks!

I'm wondering if this can be resolved with DAX formula

Aim is to ignore Quarter/Year filter in the measure

Everything is in one, single table
I have Scope = Actuals and Scope = Final Forecast
I want to use Pce per Line to calculcate my Final Forecast, but I want to manipulate the counter (multplication) by applying different period using Quarter/Year slicer 

But as you can see at below screenshot, all of a sudden in Expected orderlines (which is based on Pce per Line) from week 5 figures are not displayed anymore. It's because I do not have Scope = Actuals for week 5 and higher
But point is, I want to ignore that and for my DAX formula should be taken only Pce per Line with the period I have defined in Quarter/Year slicer

So alongside weeks all the way down it should now divide the Sum of Qnty with 2.57
I have used underneath formula to acheive it, but not working as supposed:

Expected orderliness = CALCULATE(DIVIDE(SUM(Actuals[Qnty]),[Pce per line]),ALL(Actuals[Quarter/Year]))

Pce per line = CALCULATE((DIVIDE(SUM(Actuals[Qnty]),SUM(Actuals[No of itemsstandard order]))),Actuals[Scope] = "Actuals"))

Any tip/support would be highly appreciated!

[v-rzhou-msft]

Hi @Hrtfkr ,

Here I suggest you to try ALLSELECTED() function in [Expected orderliness]. 

Expected orderliness =
CALCULATE (
    DIVIDE ( SUM ( Actuals[Qnty] ), [Pce per line] ),
    ALLSELECTED ( Actuals[Quarter/Year] )
)

If this reply still couldn't help you solve your issue, please share a sample file with me and show me a screenshot with the result you want. This will make it easier for me to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Hrtfkr]

Hey @v-rzhou-msft !

Thanks for suggesting a resolution for that case

It did guided me a little how to accomplish my goal, however I'm still stuck at below point:
I have scope = Actuals
Scope = Final Forecast
Now, I want to seperate them both and use Act_pce_per_line to calculcate the Fct_orderlines
Purpose of Quarter/Year is to e.g. select Q2.2022 and use Act_pce_per_line of this period to calculcate Fct_orderlines
So I kinda made it working with ALLEXCEPT, however now when I'm selecting any variable from Quarter/Year it returns no Fct_orderlines..

With no filters applied (that's the desired status, but I want to manipulate Fct_orderliness applying Quarter/Year PCE_per_line calculcated based on Actuals

With filter applied:

DAX I've used:

Act orderliness = CALCULATE(SUM(Merged_dataset[No of itemsstandard order]),Merged_dataset[Scope]="Actuals",ALL(Merged_dataset[Quarter/Year]))

Fctorderlines = 
var Qnty_Forecasted = CALCULATE(SUM(Merged_dataset[qnty]),Merged_dataset[Scope] = "Final Forecast")
var Pce_per_line_act = CALCULATE(DIVIDE(SUM(Merged_dataset[Qnty]),SUM(Merged_dataset[No of itemsstandard order])),Merged_dataset[Scope] = "Actuals",ALLEXCEPT(Merged_dataset,Merged_dataset[Quarter/Year]))
return
Qnty_Forecasted/Pce_per_line_act

I almost achievied it when I seperated datasets with Scope = Actuals and Scope = Final Forecast, however the other filters (e.g. country, customer) were not working anymore.. but atleast my "multiplication factor" which is PCE_Per_line based on Scope = Actuals was changing along with applying different Quarter/Year. Therefore I've decided to back to merged dataset as I belive it's achievable to tackle this within appended datasource

Desired table I created with seperating datasets (actual/forecast):

I would be grateful for any guidance on that one!