cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Hayk Frequent Visitor
Frequent Visitor

Create a versatile function

Hello,

 

Can I create a function which will calculate e.g. the ratio (or other calculation) of two variables (columns) which I will choose.

I mean the function that is not fixed for  certain  variables, but will execute the same operation for any two variables that are chosen 

differently every time.

 

Thanks,

Hayk

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

Re: Create a versatile function

you need two tables for nominator/denominator like this:

Nominator
Sales
Discount
Net Revenue
Nominator = 
VAR NominatorSlicer = SELECTEDVALUE(Nominator[Nominator])
RETURN
SWITCH(
NominatorSlicer,
"Sales", SUM(Table[Sales]),
"Discount", SUM(Table[Discount]),
//put more measures here
BLANK()
)

then a copy for Denominator
and ratio would be just

Ratio = DIVIDE([Nominator],[Denominator])



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Datanaut!

View solution in original post

7 REPLIES 7

Re: Create a versatile function

Can you provide a sample data set and provide desired output.

Super User II
Super User II

Re: Create a versatile function

you can do very flexible setup if 'measures' are in rows
Table

AttributeValue
A1
B2
C3
D4

Nominator

Nominator
A
B
C
D

 

Denominator

Denominator
A
B
C
D

the measure would look like this

Ratio = 
VAR NominatorSlicer = SELECTEDVALUE(Nominator[Nominator])
VAR DenominatorSlicer = SELECTEDVALUE(Denominator[Denominator])
VAR NominatorValue = CALCULATE(SUM('Table'[Value]),'Table'[Attribute]=NominatorSlicer)
VAR DenominatorValue = CALCULATE(SUM('Table'[Value]),'Table'[Attribute]=DenominatorSlicer)
RETURN
DIVIDE(NominatorValue,DenominatorValue)

with A-D in columns it's also achievable with SWITCH, but AFAIK you would have to hardcode each scenario


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Datanaut!

Hayk Frequent Visitor
Frequent Visitor

Re: Create a versatile function

Capture.JPG

Hayk Frequent Visitor
Frequent Visitor

Re: Create a versatile function

Here in the example data file attached, I want a function so that it calculates the ratio of the chosen two columns, for example,

if I choose "Sales & Net Revenue", it will calculate the ratio Sales/Net Revenue. If I choose Sales & Gross profit then it will calculate the ratio of Sales/Net Revenue. 

Super User II
Super User II

Re: Create a versatile function

you need two tables for nominator/denominator like this:

Nominator
Sales
Discount
Net Revenue
Nominator = 
VAR NominatorSlicer = SELECTEDVALUE(Nominator[Nominator])
RETURN
SWITCH(
NominatorSlicer,
"Sales", SUM(Table[Sales]),
"Discount", SUM(Table[Discount]),
//put more measures here
BLANK()
)

then a copy for Denominator
and ratio would be just

Ratio = DIVIDE([Nominator],[Denominator])



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Datanaut!

View solution in original post

Hayk Frequent Visitor
Frequent Visitor

Re: Create a versatile function

So I have to put all the variables that I will need to use in this function?

Super User II
Super User II

Re: Create a versatile function

if you need to have the measures in column then yes, if it would be in rows, then it's a general pattern

to my knowledge there is no such thing as dynamic column reference in DAX, i.e. translating a string value into column/table reference (equivalent of what INDIRECT does in Excel)

Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Datanaut!

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.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors