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

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

Thank you for the kudos 🙂

Proud to be a Datanaut!

7 REPLIES 7
Member

## Re: Create a versatile function

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

Super User II

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

Thank you for the kudos 🙂

Proud to be a Datanaut!

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

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

Thank you for the kudos 🙂

Proud to be a Datanaut!

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

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

Thank you for the kudos 🙂

Proud to be a Datanaut!

Announcements

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

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

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

Top Solution Authors
Top Kudoed Authors