I am trying to calculate a parameter called Performance. In TableA, we have a Total Return Rate and Date column. and seperately, TableClients
The equation is:
Performance(on a given day) = (TotalReturnRate(on a given day) / TotalReturnRate(on client start))-1
I wanted to have Performance as a calculated column, but I'm using DirectQuery Mode, so I need to use measures (as TRT on Client Start Date uses CALCULATE which is not allowed in for custom columns in DQ mode).
The issue is, TotalReturnRate(on a given day) isn't static, but TRT on a selected client's start date is.
Since I have to use a measure, I can't use the Fact.TableA[TotalReturnRate] column (columns used in measures must be wrapped un functions/aggregations)
I'm looking for a way to have a measure TotalReturnRate(on a given Date), behave like a normal column and return a different result for each date.
Can anyone help with this please?
Hi @PBIAnonUser ,
Since TableA[TotalReturnRate] is not static, but will be dynamically displayed as the user interaction with it, so we can't create a calculated column to achieve it. As the value of a calculated column is computed during data refresh and uses the current row as a context, and it does not depend on user interaction in the report. You can create a measure to display the data dynamically base on the user interactions. Please review the following links to get more details on the difference between calculated column and measure. Any comment or problem, please feel free to let me know.
In order to give you a suitable solution, could you please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It is better if you can share a simplified pbix file. Thank you.
Thanks for taking the time to review this. I've added a table of expected results for the selected index and client. Unfortunately, I cannot find the option to upload a file to the community (it mentioned in another thread that this might be because I'm relatively new?).
Anyways, I'll copy and paste the releavant tables in a reply below this.
Hi @PBIAnonUser ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a measure as below:
Performance = VAR _selddatekey = SELECTEDVALUE ( 'Date'[Datekey] ) VAR _selAdatekey = SELECTEDVALUE ( 'A'[Datekey] ) VAR _selindexcode = SELECTEDVALUE ( 'A'[IndexCode] ) VAR _returnrate = CALCULATE ( MAX ( 'A'[TotalReturnRate] ), FILTER ( ALLSELECTED ( 'A' ), 'A'[IndexCode] = _selindexcode && 'A'[Datekey] = _selddatekey ) ) RETURN DIVIDE ( SUM('A'[TotalReturnRate]), _returnrate ) - 1
This is close to what I want. I don't want to select a date in the slicer. I just want to select a client, an index, and for the measure to show performances for all dates.
If possible, I would like to have the performance done for all dates without having a date/datekey selected (or, if a date must be selected, showing performances for dates up until the selected date).
I plan on plotting Performance against 'FactA'[Date]. I've attached an example picture of what I'm looking for as a result.