cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
runnerpaul Regular Visitor
Regular Visitor

Use different filter calculations depending on comapny

Hi,

 

I have the below DAX which returns a budget calc for the current period

 

BudgetCalcForPeriod:=CALCULATE(SUM(Budget[Budget]), FILTER(Budget, Budget[YearPeriod] = MAX(BI_SalesAnalysis[YearPeriod])), FILTER( ALL( Budget[Product Group] ), 
    CONTAINS( VALUES( BI_SalesAnalysis[Product Group] ), BI_SalesAnalysis[Product Group], Budget[Product Group] ) ), FILTER( ALL( Budget[Period] ), 
    CONTAINS( VALUES( BI_SalesAnalysis[Fin Period] ), BI_SalesAnalysis[Fin Period], Budget[Period])), FILTER( ALL( Budget[Company] ), 
    CONTAINS( VALUES( BI_SalesAnalysis[Company] ), BI_SalesAnalysis[Company], Budget[Company] ) ), FILTER( ALL( Budget[Region] ), 
    CONTAINS( VALUES( BI_SalesAnalysis[Region] ), BI_SalesAnalysis[Region], Budget[Region] ) ), FILTER( ALL( Budget[Item Group] ), 
    CONTAINS( VALUES( BI_SalesAnalysis[Item Group] ), BI_SalesAnalysis[Item Group], Budget[Item Group] ) ))	

I want to edit it so that if a company field is populated with "agl" the budget is calculated as above but if it's "agp I want the same budget calculation minus this filter:

FILTER( ALL( Budget[Item Group] ), 
    CONTAINS( VALUES( BI_SalesAnalysis[Item Group] ), BI_SalesAnalysis[Item Group], Budget[Item Group] ) ))	

Is this possible and if so how do I do it?

 

Thanks

Paul

1 REPLY 1
Super User
Super User

Re: Use different filter calculations depending on comapny

I'd have to see the original data to be sure, but it should theorectically be possible by using IF.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!