cancel
Showing results for
Did you mean:
Hayk 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

## 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])`

Proud to be a Datanaut!

7 REPLIES 7
jayant_patel Member

## Re: Create a versatile function

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

Stachu Super Contributor

## Re: Create a versatile function

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

 Attribute Value A 1 B 2 C 3 D 4

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

Proud to be a Datanaut!

Hayk Frequent Visitor

## Re: Create a versatile function Hayk 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

## 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])`

Proud to be a Datanaut!

Hayk 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

## 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)

Proud to be a Datanaut!

Announcements #### 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 was a busy month in the community. Read the recap article to learn about some of the events and content.  #### Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI. Top Kudoed Authors
Users Online
Currently online: 65 members 1,279 guests
Recent signins:
• ymahdy • • edhans • ckoulismasuda • mar100 • dom99 • lc_finance • Eshra • omer_ch • sudhasanthosh • Smiral • EricFrayer • powerbiprsa • nedaroga86 