Creating a measure with SUMX and IF/OR Statements

[shiibaa]

Hi everyone,

I am pretty new to Power BI and struggling a bit. Apologies if this was asked a bunch of times, but I would appreciate any and all help!

I am trying to create a new measure for a table using multiple parameters. However, depending on a column value, I can use one OR multiple parameters. I'm having trouble writing out that formula. 

So I have: 

Total_Amount = SUMX(Table, IF(Table[ColumnA] = "abc", Table[ColumnB])) * 'Parameter'[Parameter Value]  - works fine! 

However, If ColumnA = "efg", then I want SUMX(Table, IF(Table[ColumnA] = "abc", Table[ColumnB])) * 'Parameter'[Parameter Value]  * 'Parameter2'[ParameterValue]  - also works fine by itself 

The two work fine standalone, however, when I try to combine them I am getting an error.

I tried doing: IF(ColumnA = "abc", SUMX(Table,'Table'[ColumnB]) * 'Parameter'[ParameterValue], IF(ColumnA = "efg", SUMX(Table, 'Table'[ColumnB])  * 'Parameter'[Parameter Value]  * 'Parameter2'[ParameterValue])) which gives me the error: "a single value for column in table cannot be determined"

Does anyone know what's the correct to write this out?

Thank you in advanced!!! 

[Anonymous]

Hi @shiibaa

I dont see any issues with the formula.

I hope you are trying this with calculated column. You can try SWITCH as well, but switch is also internally converted as IF function only.

Ex:

SWITCH(Cnt_Max[Score],"Good",Cnt_Max[Ticket_id]+100000,"Bad",DIVIDE(Cnt_Max[Ticket_id],Cnt_Max[Ticket_id]))

Thanks

Raj

[shiibaa]

Hi @Anonymous, 

thanks for your comment.
I am trying this with a measure because I am using a what if parameter... When I try to use SWITCH, I get the  "a single value for column in table cannot be determined" error.

[Anonymous]

Post some sample data ( copy - pastable format) with expected result.

Thanks

Raj

[shiibaa]

Sample data:

Line Item	Unit Price
A	$0.50
B	$2
C	$10

I have two What If Parameters 

Parameter 1, Parameter 2.

For Line Item A, the Total Price = Unit Price * Parameter 1.
For Line Item B, the Total Price = Unit Price * Parameter 1 * Parameter 2.

I currently have: Measure = SUMX(Table, IF(Table[Line Item] = "A", Unit Price) * Parameter 1. I am getting an error when I try to to IF([Line Item] = "A", SUMX(Table, [Unit Price]) * Parameter 1, If([Line Item] = "B", SUMX(Table, [Unit Price]) * Parameter 1 * Parameter 2)).

I hope that provides some context. Thanks!