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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
mwebergo2
Frequent Visitor

Incorrect results for Divide function

I've seen numerous posts on this topic that all seem to indicate the need for a measure instead of calculated column.  I am using measures but still getting incorrect results.  I am trying to dive the sum of Annual Expense by the sum of Book Value to get a ratio of expense to assets under management.  

mwebergo2_0-1736368168362.png

 

This seems so simple, but I am not getting correct results.  The DAX formula I am using is as follows:

Expense Ratio = DIVIDE(CALCULATE(SUM('2025 Baseline Forecast'[Annual Expense])), CALCULATE(SUM('Last Month Positions'[Book Value])),0)
6 REPLIES 6
Anonymous
Not applicable

Thanks for the replies from lbendlin and rmateo.

 

Hi @mwebergo2 ,

 

Have you solved your problem? If it is solved, please share your solution and accept it as solution or mark the helpful replies, it will be helpful for other members of the community who have similar problems as yours to solve it faster. Thank you very much for your kind cooperation!

 

Best Regards,
Zhu
 

rmateo
Frequent Visitor

  1. Verify Relationships:

    • Ensure there's a valid relationship between '2025 Baseline Forecast' and 'Last Month Positions'.
    • If these tables are not directly related, the SUM calculations might not be in the same filter context.
  2. Test Individual Components:

    • Create separate measures to confirm each component is returning expected results:
      DAX
      Copy code
      TotalExpense = SUM('2025 Baseline Forecast'[Annual Expense]) TotalBookValue = SUM('Last Month Positions'[Book Value])
    • Use these measures in a table visual alongside your dimensions to ensure they calculate correctly.
  3. Validate Filter Context:

    • If your data uses different dimensions (e.g., time, region, category), ensure both tables are being filtered correctly by the same dimensions.
  4. Row-Level Aggregation Check:

    • Ensure the calculation doesn’t suffer from granularity mismatch. For example, if Book Value is stored at a more granular level (e.g., daily) than Annual Expense (e.g., yearly), aggregations might not align correctly.

Adjusted Formula:

If your data model has no issues and you simply need the correct context for the division:

DAX:
Expense Ratio = DIVIDE( CALCULATE(SUM('2025 Baseline Forecast'[Annual Expense])), CALCULATE(SUM('Last Month Positions'[Book Value])), 0 )

Additional Scenarios:

  • If Tables Are Not Related: Use CROSSFILTER or ALL to manipulate filter context:

    DAX
    Expense Ratio = DIVIDE( CALCULATE(SUM('2025 Baseline Forecast'[Annual Expense]), ALL('Last Month Positions')), CALCULATE(SUM('Last Month Positions'[Book Value]), ALL('2025 Baseline Forecast')), 0 )
  • If You Need a Weighted Average: Use a more complex formula to handle weighting:

    DAX
    Expense Ratio = DIVIDE( SUMX( 'YourCommonDimensionTable', CALCULATE(SUM('2025 Baseline Forecast'[Annual Expense])) ), SUMX( 'YourCommonDimensionTable', CALCULATE(SUM('Last Month Positions'[Book Value])) ), 0 )

Visual Troubleshooting:

  • Add the Expense Ratio measure to a matrix visual with relevant dimension columns (e.g., year, category) to confirm results at different levels of granularity.

If the issue persists, share details about the data relationships, granularity, or any specific filters that may affect calculations.

mwebergo2
Frequent Visitor

Here's a snip of the dashboard.  The expense ratio card (circled in blue) is the measure I am trying to depict.  It is the results of dividing the two cards just above it.  

mwebergo2_0-1736374203649.png

 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

mwebergo2
Frequent Visitor

No

lbendlin
Super User
Super User

Do you display that measure next to a column from the Calendar Table?

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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