Forum Discussion

Hrtfkr's avatar
Hrtfkr
Regular Visitor
3 years ago

Ignore filter in measure

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 = Fin...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
Hrtfkr
Regular Visitor
3 years ago

Hey Anonymous !

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!