Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
Can someone please help me, how I should define below solution in Power BI.
Data
1) I have Trade table, which has trade related information for all executed dates.
2) I have Performance table which has PnL information per day.
Requirements
1) User can select any date range using date range control.
2) Let's say user selected date range between 1st JAN and 12th FEB.
In this case,
I want to SUM UP all trades between particular dates
I want to SUM UP all performance between particular dates
Once I have those SUM, I would like to join table based on three keys to display my final data in report.
I am not joing table before doing SUM because, there could be case wherein Trade tables doesn't have Trade information on particular day but Performance table may have information for that particular day(considering trade was open and PnL generated for historical trade).
Can you please guide me in terms of implementing above solution?
regards,
Digant
Solved! Go to Solution.
Hi @digant,
I tried changing both Crosss filter direction Performance - Trade & Trade - Calendar relationships to Single, but still can not define relationship between( not even single) Perfmance - Date table.
In this scenario, you can create an inactive relationship between Performance - Date table like below.
Then you should be able to create a measure to calculate the sum of Performance[Pnl] using USERELATIONSHIP Function (DAX), and show the measure on the report with other columns.
PnlVolume = CALCULATE ( SUM ( Performance[Pnl] ), USERELATIONSHIP ( 'Date'[Date], Performance[PnlDate] ) )
Here the sample pbix file for your reference.
Regards
Hi @digant,
I recommend you create a Date/Calendar table. You can do this in DAX using the CALENDARAUTO() function
eg.
Dates = CalendarAuto()
Create a relationship between your Trade table and the Dates table
Then create a relationship between your Performance Table and the Dates table.
Use the Date field from your Dates table in a Slicer. This will give you the option to set date ranges.
Once you have that, simply create SUM measures on each of your Trade and Performance tables and if you drag these to a visual, along with Date from your Dates table, I think you will be close to what you need.
How does that sound?
The key is you don't need a relationship between Trade and Performance from the info you have given.
Hi Phil,
Thank you for your reply !!
Although I have created table with dates and defined relationships, SUM is not calculated properly.
I wanted SUM in Performance, which will be utilized Trade table to be grouped by TradeID between two dates that I am passing but it always groups on Date rather than TradeID.
Moreover, I can not define any relationship between Trades and Performance table on TradeID.
TRADE Table
TradeID | TradeVolume | TradeExecuted |
1 | 5000 | 1/01/2016 |
2 | 10000 | 2/01/2016 |
3 | 7000 | 20/01/2016 |
Performance Table
TradeID | Pnl | PnlDate |
1 | 100 | 1/01/2016 |
1 | 200 | 2/01/2016 |
1 | 300 | 15/01/2016 |
2 | 3000 | 2/01/2016 |
POWER BI Table looks (for date btween 1st JAN and 20th JAN)
Current
TradeExecuted | TradeID | TradeVolume | Pnl |
1/01/2016 0:00 | 1 | 5000 | 100 |
2/01/2016 0:00 | 2 | 10000 | 3200 |
20/01/2016 0:00 | 3 | 7000 |
I would like to see below data.
TradeExecuted | TradeID | TradeVolume | Pnl |
1/01/2016 0:00 | 1 | 5000 | 600 |
2/01/2016 0:00 | 2 | 10000 | 3000 |
20/01/2016 0:00 | 3 | 7000 |
Thank you in advance !!
regards,
Digant
Hi @digant
I have a working solution but only IF the TradeID column is unique in your TRADE table.
If it is, create a relationship between Trade and Performance on TradeID (Trade = oneside and Performance = Many side)
Then just add the following calculated column to the Trade Table
New Column = CALCULATE(SUM('Performance'[Pnl]))
Thank you for your reply, but I can not add all three relationship together.
1) Many to one relationship between Trade and Calendar table
2) Many to one relationship between Performance and Calendar table
3) Many to one relationship between Performance to Trade table on TRADEID
If I deactivate Performance and Calendar relation to add third relation then Performance data doesn't respect date filters.
TRADE
TradeID | TradeVolume | TradeExecuted |
1 | 5000 | 1/01/2016 |
2 | 10000 | 2/01/2016 |
3 | 7000 | 20/01/2016 |
4 | 12000 | 1/01/2016 |
Performance
TradeID | Pnl | PnlDate |
1 | 100 | 1/01/2016 |
1 | 200 | 2/01/2016 |
1 | 300 | 15/01/2016 |
2 | 3000 | 2/01/2016 |
4 | 300 | 2/01/2016 |
4 | 400 | 3/01/2016 |
4 | 500 | 7/01/2016 |
4 | 900 | 1/01/2016 |
POWER BI(Date selected 1st JAN)
TradeExecuted | TradeID | TradeVolume | Pnl |
1/01/2016 0:00 | 1 | 5000 | 600(Correct 100) |
1/01/2016 0:00 | 4 | 12000 | 2100(Correct 0) |
Thank you !!
regards,
Digant
Are any of the relationships BI -Directional? Make sure they are set to Single
Hi @Phil_Seamark,
I tried changing both Crosss filter direction Performance - Trade & Trade - Calendar relationships to Single, but still can not define relationship between( not even single) Perfmance - Date table.
regards,
Digant
Hi @digant,
I tried changing both Crosss filter direction Performance - Trade & Trade - Calendar relationships to Single, but still can not define relationship between( not even single) Perfmance - Date table.
In this scenario, you can create an inactive relationship between Performance - Date table like below.
Then you should be able to create a measure to calculate the sum of Performance[Pnl] using USERELATIONSHIP Function (DAX), and show the measure on the report with other columns.
PnlVolume = CALCULATE ( SUM ( Performance[Pnl] ), USERELATIONSHIP ( 'Date'[Date], Performance[PnlDate] ) )
Here the sample pbix file for your reference.
Regards
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |