cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Stachu Super Contributor
Super Contributor

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!

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.

Stachu Super Contributor
Super Contributor

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!

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. 

Stachu Super Contributor
Super Contributor

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!

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?

Stachu Super Contributor
Super Contributor

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!

Proud to be a Datanaut!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 65 members 1,279 guests
Please welcome our newest community members: