Complex Dax use case

[Rachel_123]

 I have this complex use case, I need to get a list of projects or flag them in DAX based on the following:

   -  If a project has multiple expense types then flag :

            The project that has 5% or more in variance in more than one expense type but variance in $ is within 85k for those  expense types.

- if project has one expense type then flag it when variance is above 5% but variance in $ is less than 85K.

In the table below the projects MNO and EFG will be flagged because they fall within that criteria, project ABC won't because one of it's variance that is above 5% is also above 85K

I used the following formla to flag the ones that have more than one expense type:

Column = Var __MultiExpenseType= CALCULATE(DISTINCTCOUNT('Projects Summary'[Expense Type]), ALLEXCEPT('Projects Summary','Projects Summary'[Project])) RETURN __MultiExpenseType > 1

Any help will be appreciated!

[vanessafvg]

ok i think i managed to figure it out, there might might be a simpler way of doing it but here it is.

i created a calculated column

CriteriaCol =
VAR result =
CALCULATE (
SUMX (
VALUES ( 'Projects Summary'[Project] ),
VAR varperc =
MAX ( 'Projects Summary'[Variance in %] )
VAR varcurr =
MIN ( 'Projects Summary'[Variance in $] )
RETURN
IF ( varperc > 0.05 && varcurr < 85000, 1, 0 )
)
)
RETURN
result

then i created the flag

Flag =
VAR criteria =
SUMX (
VALUES ( 'Projects Summary'[Project] ),
VAR project =
SELECTEDVALUE ( 'Projects Summary'[Project] )
VAR expensetype =
SELECTEDVALUE ( 'Projects Summary'[Expense Type] )
RETURN
CALCULATE (
MIN ( 'Projects Summary'[CriteriaCol] ),
ALL ( 'Projects Summary' ),
'Projects Summary'[Project] = project
&& 'Projects Summary'[Expense Type] = expensetype
)
)
RETURN
criteria

see file attached

[vanessafvg]

it depends on how you are creating your data, what other calculated columns do you have in your data?  

[Rachel_123]

So I created a table exactly like the example I posted in my question, with just these columns and no additional calculated column , I implemnted your solution and I am still getting circular dependency , I am not sure if I am doing somethign different than you 😞 

[Rachel_123]

so my table has the following columns
Date, Project, Actuals, Current Budget, Expense Type, Var to budget ($) and Var to budget %

both the Var to budget $ and % are calculated 
Var to budget ($) = 

if('Projects Summary'[Actuals] <> blank(), ABS('Projects Summary'[Current Budget]-'Projects Summary'[Actuals]),blank())
Var to budget (%) =  ABS(DIVIDE('Projects Summary'[Var to Budget],'Projects Summary'[Current Budget]))
But the error I get is that circular dependency between the 2 new columns that you suggested I create, could it be because the % column depends on the $ column?

[Rachel_123]

thank you so much for putting the all this effort into it, I followed your solution step by step but when I created the flag column I got an error of circular dependency because I am using the criteria column (Var Criteria Threshold), any ideas why I am getting this error?

[vanessafvg]

what are you struggling specifically, bringing all the requirements together? also can you provide the data in text format not a screenshot.

[Rachel_123]

The part I am strugging with is when the project has 2 or more expense categories with 5%, I am able to get a result if it's just one expense category bigger than 5%:

Final flag = if([Flag with multi expense] =TRUE(),if(Varince %>5 && Variance in $ <85000,"Y","No"),"No")

Project	Date	Expense Type	Variance in $	Variance in %
ABC	4/3/2023	X	93000	6%
ABC	3/3/2023	Y	70000	10%
ABC	4/3/2023	Y	5000	1%
KLM	2/3/2023	Z	12000	2%
MNO	4/3/2023	X	6500	7%
EFG	2/3/2023	Z	50000	10%
EFG	4/3/2023	M	60000	12%
EFG	3/3/2023	Y	1000	3%

[vanessafvg]

as i am going through this i am little confused about your conditions;

for multiple rows must all expenses associated to that project be  >= 5% or just >5% and < 85000 or <=85000 

for a single row is it  > 5% and < 85000?

I am trying to under why these 2 rows dont qualify for flagging.

[Rachel_123]

Not all rows, if 2 or more rows associated with that project have more than 5% and those same rows are under 85000. Because Project ABC have a row that has over 5% ( first row 6%) and 93000 it shouldn't show up on the list.