topic Re: IF Statement plus Multiplication using static number in DAX Commands and Tips

IF Statement plus Multiplication using static number

Anonymous — Thu, 18 May 2023 09:17:59 GMT

Hi all!

Sorry if it's a silly one (because I feel like it should be a pretty straight forward one), but can I have help with the below please.

I'm trying to replicate the formulae below in Power BI:

=IF(Y2="Y",(AL2*0.25),0)

Where:

Y2 = would be the column in Power BI with the criteria I want to meet

AL2 = the second column I want to multiply the value by the Gift Aid rate

0.25 = 25% of Gift Aid rate (static number, not a column)

Basically, I need to meet a condition in a column so I can multiply the second one based on a static rate number.

Is it because the number I need to multiply by is not a column instead?

Thanks,

Ana

Re: IF Statement plus Multiplication using static number

pratyashasamal — Thu, 18 May 2023 09:46:49 GMT

Hi Anonymous ,
Try this calculation ,

Measure = IF(SUM('Table'[Y2])= "Y",CALCULATE(SUM('Table'[AL2])*0.25)),0)

if you want to have 0.25 or 25 % in column you can do that just you need to aggregate and then multiple .
For example :-
Where gift = 25 %
Measure = IF(SUM('Table'[Y2])= "Y",CALCULATE(SUM('Table'[AL2])*(SUM('Table'[Gift])/100)),0)

Thanks ,
Pratyasha Samal
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

Re: IF Statement plus Multiplication using static number

Anonymous — Thu, 18 May 2023 09:56:38 GMT

Thansk for that. Just tried but it's saying it's incorrect:

Thanks,

Ana

Re: IF Statement plus Multiplication using static number

pratyashasamal — Thu, 18 May 2023 10:01:15 GMT

Measure = IF('Table'[Y2]= "Y",CALCULATE(SUM('Table'[AL2])*0.25),0)

Try this

Re: IF Statement plus Multiplication using static number

Anonymous — Thu, 18 May 2023 10:15:01 GMT

Didn't work again:

I'll probably create an extra column for the 25%.

Re: IF Statement plus Multiplication using static number

pratyashasamal — Thu, 18 May 2023 10:22:56 GMT

No need what is data type of GiftAid column ?

Re: IF Statement plus Multiplication using static number

pratyashasamal — Thu, 18 May 2023 10:27:56 GMT

Try this once,
This will work only from the Y2 column (GiftAid) column datatype is text and it also has a value of Yes in it . Exact function will match the Y2 column with Yes text and return a True / false column .

Measure = IF(Exact('Table'[Y2],"Yes"),CALCULATE(SUM('Table'[AL2])*0.25),0)

Re: IF Statement plus Multiplication using static number

Anonymous — Thu, 18 May 2023 10:32:22 GMT

Sorry, also didn't work. After the "Exact", it doesn't allow me to chose a table.

GiftAid column is Text

Amount column is Decimal number.

Re: IF Statement plus Multiplication using static number

pratyashasamal — Thu, 18 May 2023 10:34:30 GMT

Measure = IF('Table'[Y2] == "Yes",CALCULATE(SUM('Table'[AL2])*0.25),0)

Re: IF Statement plus Multiplication using static number

Anonymous — Thu, 18 May 2023 10:40:10 GMT

Again, it doesn't allow me to select a table, only the other measures I have.

Would any screen shot help? Not sure of what to be honest hehe I think the issue is the aggregation after the IF, isn't it?

Re: IF Statement plus Multiplication using static number

pratyashasamal — Thu, 18 May 2023 10:52:47 GMT

This is ss ,
Measure 3 is where is am checking if the condition is Yes or not .
Then multiplying it with my sales value .
and Measure 2 is a simple measure with "Yes" or "No" values.

Re: IF Statement plus Multiplication using static number

Anonymous — Thu, 18 May 2023 11:02:39 GMT

Below is an example of my data:

This is what I get when trying your latest statement:

Even if I type the table/column, still it doesn't work. Both columns ("GiftAid" and "amount") used for the calculation are from the same table. But I don't think it should matter, should it?

Re: IF Statement plus Multiplication using static number

pratyashasamal — Thu, 18 May 2023 11:16:44 GMT

Please check once what is the datatype of GiftAid column and change it to text if it not a text .

Re: IF Statement plus Multiplication using static number

Anonymous — Thu, 18 May 2023 11:22:00 GMT

It is a Text. And it's not a measure, it's a column.

Re: IF Statement plus Multiplication using static number

Anonymous — Mon, 22 May 2023 09:34:41 GMT

I found the answer here -> https://www.enjoysharepoint.com/power-bi-measure-multiply/ (Power BI Measure multiply by 100).

_GA Income Calcs = IF(SELECTEDVALUE('Single Donations'[GiftAid]) = "Yes", CALCULATE(SELECTEDVALUE('Single Donations'[amount]) * 0.25, FILTER('Single Donations', 'Single Donations'[GiftAid] = "Yes")), 0)

Thank you so much for all your help with this, pratyashasamal .

Re: IF Statement plus Multiplication using static number

LukeFPowerBI — Wed, 19 Jun 2024 08:18:52 GMT

I am trying to use the basic element of this and struggling its not accepting the argument for me

GA Income Calcs = IF(SELECTEDVALUE('vw_Transactions'[GiftAidStatus]) = "TRUE", CALCULATE(SELECTEDVALUE('vw_Transactions'[DiscountedValue]) * 0.25, FILTER('vw_Transactions','vw_Transactions'[GiftAidStatus]) = "TRUE")),0)

i get this
The syntax for ',' is incorrect. (DAX(IF(SELECTEDVALUE('vw_Transactions'[GiftAidStatus]) = "TRUE", CALCULATE(SELECTEDVALUE(vw_Transactions[DiscountedValue]) * 0.25, FILTER(vw_Transactions,vw_Transactions[GiftAidStatus]) = "TRUE")),0))).

I honestly cant see what i am doing wrong because based on the info on this post and aligning to my own data this should work? I am new to powerbi only been a user for a few weeks.

I am new t

Re: IF Statement plus Multiplication using static number

LukeFPowerBI — Wed, 19 Jun 2024 08:27:43 GMT

Can you help I have copied your answer but getting errors

I am trying to use the basic element of this and struggling its not accepting the argument for me

I honestly cant see what i am doing wrong because based on the info on this post and aligning to my own data this should work? I am new to powerbi only been a user for a few weeks.

Re: IF Statement plus Multiplication using static number

LukeFPowerBI — Wed, 19 Jun 2024 08:44:42 GMT

I also tried the second tip

Gift Aid Income = IF('vw_Transactions'[IsGiftAided]= "TRUE",CALCULATE(SUM('vw_Transactions'[DiscountedValue])*0.25),0)

For some reason the first past the IF('vw_Transactions'[IsGiftAided]= "TRUE" seems to only want to pick up measures that are created and not table data.

Re: IF Statement plus Multiplication using static number

Anonymous — Wed, 19 Jun 2024 10:14:19 GMT

Hi LukeFPowerBI , please check my response to my own post here - I found a solution on that link I shared in my response. There is also the calculation I used.

From what I rmember, that worked fine.

Thanks,

Ana

Re: IF Statement plus Multiplication using static number

LukeFPowerBI — Wed, 19 Jun 2024 10:19:53 GMT

Hi I also tried this

I also tried the second tip

Gift Aid Income = IF('vw_Transactions'[IsGiftAided]= "TRUE",CALCULATE(SUM('vw_Transactions'[DiscountedValue])*0.25),0)

For some reason the first past the IF('vw_Transactions'[IsGiftAided]= "TRUE" seems to only want to pick up measures that are created and not table data.