DAX - SUMX with multiple filters.

[mallap849]

Hello Everyone,

I have a table named 'VP-Warehouse Exp in this table I am working with variabels i.e., ORG and Expense. I want to create a varibale 5000cc Exp with multiple if statesmese. For ex. if the Org is 2244 then 5000cc Exp = Expense * 0.08, if Org is 2224 then 5000cc Exp = Expense*0.34, if Org is 2234 then 5000cc Exp = Expense*0.21, and if Org is 2226 then 5000cc Exp = Expense*0.37 and all else 0. 

This is the end table I want to create:

ORG	Expense	5000cc Exp
2244	$   63,317.24	$     5,065.38
2442	$     1,678.61
2224	$ 130,895.95	$   44,504.62
2413	$   18,801.47
2422	$ 158,095.38
2414	$ 322,939.47
2410	$   80,716.58
2234	$   98,379.73	$   20,659.74
2226	$ 481,761.99	$ 178,251.94

I am using this DAX

5000cc Exp =
SUMX(FILTER('VP-Warehouse Exp','VP-Warehouse Exp'[ORG] = 2244),'VP-Warehouse Exp'[Expense]*0.08)

But I cannot seem to put a comma and enter more criteria. Please advice.

Thank you!

[TheoC · Accepted solution]

Hi @mallap849 

You can use SWITCH TRUE in a calculated column.  See attached PBIX file.

5000cc Exp NEW = 

VAR _1 = 

SWITCH ( 
    TRUE () ,
        'Table'[ORG] = 2244 , 'Table'[Expense] * 0.08 ,
        'Table'[ORG] = 2234 , 'Table'[Expense] * 0.21 ,
        'Table'[ORG] = 2226 , 'Table'[Expense] * 0.37 , 
        0 )

RETURN

_1

The output will be what you're after:

Just make sure that the ORG column is formatted to a Number otherwise you will need to put the ORG code in inverted commas like "2244" 

5000cc Exp NEW = 

VAR _1 = 

SWITCH ( 
    TRUE () ,
        'Table'[ORG] = "2244" , 'Table'[Expense] * 0.08 ,
        'Table'[ORG] = "2234" , 'Table'[Expense] * 0.21 ,
        'Table'[ORG] = "2226" , 'Table'[Expense] * 0.37 , 
        0 )

RETURN

_1

Hope this helps!

Theo

[mallap849]

@TheoC The swich command worked. Thank you!

[ppm1]

You can try this instead, and add additional conditions inside the CALCULATE.

5000cc Exp =
CALCULATE (
SUM ( 'VP-Warehouse Exp'[Expense] ) * 0.08,
'VP-Warehouse Exp'[ORG] = 2244
)

Pat

[mallap849]

https://1drv.ms/u/s!AiHR2qZdQykFgVfhauCn54nLx1LF?e=qdNWbt 

Hi @ppm1.,

I tried to replace your comman in the varaibel 5000cc Exp in table VP-Warehouse Exp. But I could not get to work.

So far I have got ORG = 2244 then 5000cc Exp = Expense * 0.08, if Org = 2224. But I also want to add other criteria's like if ORG = 2234 then 5000cc Exp = Expense*0.21, ORG = 2234 then 5000cc Exp = Expense*0.34, and if ORG is 2226 then 5000cc Exp = Expense*0.37 and all else 0. 

Is there a way I can add these criterias?

Thank you,

Priyanka 

[ppm1 · Accepted solution]

I reread your post and realize I didn't read close enough. You can use a SWITCH approach as already suggested, or you can update your model with another simple table like this one, with a relationship to your table on the Org column. You can easily add it with the Enter Data functionality (call it OrgFactors).

Once you have that,  you can use a measure like this to get your result.

OrgFactorsSumx =
SUMX (
    OrgFactors,
    OrgFactors[Factor] * CALCULATE ( SUM ( 'VP-Warehouse Exp'[Expense] ) )
)

Pat

[mallap849]

Thank you! 

[mallap849]

Hi Theo,

Thank you, for your prompt respone. I tried it but it is giving me an error. I will be gone for a vacation for the remaining week so I will repond to you next week.

Regards,

Priyanka 

[TheoC]

Hi @mallap849 I can assure you it works.  Just make sure that the column Formats are correct.  I added a Power BI file for you.  Use that as guidance.

Have a great vacation and if you still struggle with solution I put forward once you're back, let me know.

Theo

[mallap849]

https://1drv.ms/u/s!AiHR2qZdQykFgVfhauCn54nLx1LF 

Hi Theo,

I have attached the sample file for reference. I have a table called Vehicle Parts from this table I created another table called VP-Warehouse Exp table. It is in this table that I want to create a variable and the command did not work for some reason. Also I tried in the original table and then tried to group by but it didnt work agian. Can I use another command ?

Thank you.

[TheoC · Accepted solution]

Hi @mallap849 

You can use SWITCH TRUE in a calculated column.  See attached PBIX file.

5000cc Exp NEW = 

VAR _1 = 

SWITCH ( 
    TRUE () ,
        'Table'[ORG] = 2244 , 'Table'[Expense] * 0.08 ,
        'Table'[ORG] = 2234 , 'Table'[Expense] * 0.21 ,
        'Table'[ORG] = 2226 , 'Table'[Expense] * 0.37 , 
        0 )

RETURN

_1

The output will be what you're after:

Just make sure that the ORG column is formatted to a Number otherwise you will need to put the ORG code in inverted commas like "2244" 

5000cc Exp NEW = 

VAR _1 = 

SWITCH ( 
    TRUE () ,
        'Table'[ORG] = "2244" , 'Table'[Expense] * 0.08 ,
        'Table'[ORG] = "2234" , 'Table'[Expense] * 0.21 ,
        'Table'[ORG] = "2226" , 'Table'[Expense] * 0.37 , 
        0 )

RETURN

_1

Hope this helps!

Theo