Forum Discussion

aaftab42's avatar
aaftab42
Frequent Visitor
1 year ago

Issue with Calculating Drawdown with Filters in Power BI

Hi Community Members, I'm currently working on a Power BI project and could use some guidance on optimizing my drawdown calculations. Here's a summary of my approach and the challenge I'm facing: C...
Need Help
TomMartens's avatar
TomMartens
Super User
1 year ago

Hey aaftab42 ,

 

please provide a pbix that contains sample data, but still reflects your semantic model (tables, relationships, calculated columns, and measures). Upload the pbix to OneDrive, Google Drive, or Dropbox and share the link. If you are using spreadsheet to create the sample data instead of the manual import method, share the spreadsheet as well.

 

Do not forget to describe the expected result based on the sample data you provide.

 

Regards,

Tom

aaftab42's avatar
aaftab42
Frequent Visitor
1 year ago

Hi Tom,

I hope this message finds you well.

I have encountered an issue with the calculation of drawdown in my Power BI report, and I would appreciate any guidance or advice you could offer.

Details of the Issue:

  • Data Structure: I have three files:
    • four_strategy: This is the fact table.
    • indices: This is a category table.
    • strategy: This is a sub-category table.
  • Measures Involved:
    • cumulativeprofit
    • running max profit
    • dailydrawdown

Problems:

  • If the first day shows a negative value in profit and loss, it does not mark it as a drawdown.
  • Applying filters to specific years or days results in incorrect calculations due to the use of the ALL function, which does not adhere to the filters. Using ALLSELECTED to fix this issue leads to memory exceed errors.

Objective:

I would like to have an accurate drawdown calculation in a line chart that reflects the drawdown under different filters, similar to how the total profit is displayed.

Files and Data:

I have created a sample .pbix file that includes the tables, relationships, calculated columns, and measures. Additionally, I am providing spreadsheets with sample data to better illustrate the issue. The files are available at the following link:

Google Drive Link

Expected Result:

The goal is to accurately calculate and display the drawdown in a line chart, while correctly responding to different filters applied to the data, such as specific years or days, without causing memory issues.

Thank you in advance for your support and assistance.

Best regards,
Aaftab Ahmad