Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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])
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
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.
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])
So I have to put all the variables that I will need to use in this 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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |