cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GoingIncognito Advocate II
Advocate II

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 Solution Supplier
Solution Supplier

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 Solution Sage
Solution Sage

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 Advocate II
Advocate II

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 Solution Sage
Solution Sage

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 Solution Sage
Solution Sage

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 Advocate II
Advocate II

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 Solution Sage
Solution Sage

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 Advocate II
Advocate II

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?

Highlighted
rajulshah Solution Sage
Solution Sage

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 Solution Supplier
Solution Supplier

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors