topic Re: Creating a Measure by using a fixed value in DAX Commands and Tips

Creating a Measure by using a fixed value

agius — Thu, 28 May 2020 07:22:32 GMT

Hi,

I need to create a simple measure but cannot seem to make it work. I need to divide 2 columns and then multiply the answer by 100. So it looks something like this:

CTR = (Clicks / Impressions) * 100

So far I managed this much

CTR =

(DIVIDE(

SUM('Adform - Daily'[Clicks]),

SUM('Adform - Daily'[Impressions)]))

)

Any suggestions?

Thanks!

Re: Creating a Measure by using a fixed value

AlB — Wed, 27 May 2020 16:18:24 GMT

Hi agius

So what is the problem exactly with the measure that you put together? You just need to multiply by 1000 but other than that it looks fine, given the info provided.

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Re: Creating a Measure by using a fixed value

ryan_mayu — Thu, 28 May 2020 05:50:40 GMT

agius

Did you get an error message or not get the expected result?

The brackets in your formula is not correct. Is that a typo error?

CTR =
(DIVIDE(
SUM('Adform - Daily'[Clicks]),
SUM('Adform - Daily'[Impressions)]))
)

Re: Creating a Measure by using a fixed value

agius — Thu, 28 May 2020 07:29:43 GMT

ryan_mayu Your suggestion helped! However, I still need to multiply by 100. I do not receive any error but I do not have the expected results.

AlB Now, I have this

CTR =
(DIVIDE(
SUM('Adform - Daily'[Clicks]),
SUM('Adform - Daily'[Impressions)]))
) *100

But it doesn't work. Any suggestions? 🙂

Re: Creating a Measure by using a fixed value

ryan_mayu — Thu, 28 May 2020 07:07:26 GMT

agius

Could you please provide some screenshots or demo file?

What's your expected results and what result you are getting now?

Thanks and BR

Re: Creating a Measure by using a fixed value

agius — Thu, 28 May 2020 07:29:08 GMT

ryan_mayuI have the below and i need to have the (Total of Clicks / Total of Impressions) *100.

However, I cannot use the calculated fields as it takes the average instead of sum and then divide. For the below, the answer would be 0.42%. But obviously, I have larger dataset.

Hope this helps 🙂

Re: Creating a Measure by using a fixed value

ryan_mayu — Thu, 28 May 2020 07:56:38 GMT

agius

Finally, I figured out your issue.

If you want to change the decimal number to percentage, you can't multiple 100 in the coding. That will make the result be larger.

You need to choose the measure and change to percentage format.

Re: Creating a Measure by using a fixed value

agius — Thu, 28 May 2020 08:28:39 GMT

ryan_mayu This is great! Thanks 🙂

This solves this issue. But I have similar situations where i need to multiply by a fixed number. I find it hard to believe how it is not possible to multiply by a fixed number. Maybe, I can just add a column with this fixed value and multiply my answer to it. I guess that might work. If you know of any easier solution please let me know 🙂

Re: Creating a Measure by using a fixed value

ryan_mayu — Thu, 28 May 2020 08:30:43 GMT

agius

Please provide the screenshots or demo file. Let me know what the expected result as well.

Thanks and BR

Ryan