cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
GoingIncognito Regular Visitor
Regular Visitor

How to save percentage as a variable in DAX

Hi.

My question is simple: How do I save a percentage as a variable in DAX? In order to count Sharpe's ratio I'd need to perform a following calculation:
Profit - (-0,25%) / volatility. How do I save the -0,25% as a variable? Only way I figured was to make a measure containing -0,25 and then change the data type to percentage.

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
EricHulshof Member
Member

Re: How to save percentage as a variable in DAX

I think you need to understand a bit more about how percentages are saved, so here we go.
When a percentage is saved in Power BI it is saved as a decimal number (0,75) = 75%. When you select the modeling as percentage power bi is very smart and knows you want to see 75%. So it does that for you. 
When calculating tho you would (most of the cases) use 0.75. Because if i want to calculate 75% of 1000 i can do 1000*0.75=750.
hence he said you might want to do it *100. (0.75*100=75%)


So to get back to your initial question when you save a measure with -0.25 and make it a percentage. The percentage is -25%. Calulating with -0.25% would be -0.0025.

i hope this helps 🙂

View solution in original post

11 REPLIES 11
rajulshah Senior Member
Senior Member

Re: How to save percentage as a variable in DAX

Hello @GoingIncognito,

 

You can either save the format as Percentage:

VAR PercentValue = FORMAT([Your Expression],"Percent")

 

But if you want to use that variable in a calculation, you can use it like:

VAR PercentValue = [Your Expression]*100

 

Let me know if this is not what you want.

GoingIncognito Regular Visitor
Regular Visitor

Re: How to save percentage as a variable in DAX

Do I follow your reasoning if I say that, Format() returns a string (eventhough I want a number). BUT as DAX is such a high level language, it automagically treats a string as a number when I use it in a calculation? 

rajulshah Senior Member
Senior Member

Re: How to save percentage as a variable in DAX

Yes, FORMAT returns string, you can either multiply by 100 or you can also use VALUE(FORMAT([Your Expression,"Percent")), but not sure if it works. Easiest solution is to multiply with 100.

rajulshah Senior Member
Senior Member

Re: How to save percentage as a variable in DAX

Hello @GoingIncognito, did the solution work for you?

 

If yes, please mark the answer as solution so that others can find the solution easily.

 

Thanks.

GoingIncognito Regular Visitor
Regular Visitor

Re: How to save percentage as a variable in DAX

The format option doesn't work - unfortunately - and I'm too stupid a person to comprehend how multiplyin by a hundred would help?

rajulshah Senior Member
Senior Member

Re: How to save percentage as a variable in DAX

Didn't understand your question. Can you provide the measure here where you need to convert into Percentage?

GoingIncognito Regular Visitor
Regular Visitor

Re: How to save percentage as a variable in DAX

Oh yes, indeed, I should have done so in the first place! Thank you for your patience.

 

My measure is something like:


Sharpe  =

var risk free interest = (how can I save a percentage value here?)

return
divide( (3,3% - risk free interest ) , 10,8%)

 

I get the values 3,3% and 10,8% as measures. And for a measure I can set the data format to be percentage. But risk free interest I would like to input as a variable within the measure. How can I save a percentage value to a variable?

rajulshah Senior Member
Senior Member

Re: How to save percentage as a variable in DAX

@GoingIncognito,

 

So, if the value is 0.25, if you convert it into Percentage it is 25%, right?

 

So, you can just do 0.25*100 = 25.

 

Please let me know if this wasn't clear enough to understand.

EricHulshof Member
Member

Re: How to save percentage as a variable in DAX

I think you need to understand a bit more about how percentages are saved, so here we go.
When a percentage is saved in Power BI it is saved as a decimal number (0,75) = 75%. When you select the modeling as percentage power bi is very smart and knows you want to see 75%. So it does that for you. 
When calculating tho you would (most of the cases) use 0.75. Because if i want to calculate 75% of 1000 i can do 1000*0.75=750.
hence he said you might want to do it *100. (0.75*100=75%)


So to get back to your initial question when you save a measure with -0.25 and make it a percentage. The percentage is -25%. Calulating with -0.25% would be -0.0025.

i hope this helps 🙂

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors