Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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
Stachu
Community Champion
Community Champion

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 🙂

View solution in original post

7 REPLIES 7
Stachu
Community Champion
Community Champion

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 🙂

Anonymous
Not applicable

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

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
Community Champion
Community Champion

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 🙂

Hayk
Frequent Visitor

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

Stachu
Community Champion
Community Champion

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 🙂

Capture.JPG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.