- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Incorrect DIVIDE result when dividing two meausres
Hi community!
I am facing an issue with my DAX DIVIDE measure and would appreciate your insights on this .
Data Model Overview:
- Date table: Marked as a date table with unique date fields.
- dim_google:
- Columns: 'date', 'page location', 'screen_page_views'
- Data transformation: Originally had timestamp date fields which I converted to Date format and grouped by 'date' & 'page location' to have unique page/date combinations.
- fact_items: Columns include 'date' and 'id'.
Relationships Set:
- Date[Date] has a 1-many relationship with fact_items[date]
- Date[Date] has a 1-many relationship with dim_google[date]
- dim_category[id] has a 1-many relationship with fact_items[category_id], I am using [code] field to filter the fact_items with the filters pane.
Measures Created:
Items_Purchased: CALCULATE( DISTINCTCOUNT(fact_items[id]) )
Add_to_Cart: CALCULATE( SUM( 'GA4'[screen_page_views] ), CONTAINSSTRING( 'GA4'[page_location], "/cart_item" ) || CONTAINSSTRING('GA4'[page_location], "cart") )
Conversion Rate: DIVIDE( [Items_Purchased], [Add_to_Cart], BLANK() )
Problem:
- When I evaluate the two measures ([Items_Purchased] and [Add_to_Cart]) separately, they provide correct values for whole periods.
- However, using the above measures, my conversion rate for 2022-04-20 is being calculated as 263.2% when I expect it to be 150/178 = 84.3%. I don't notice any nulls or 0 in numerator and denominator.
- I also noticed that I'm not able to see the conversion rate for dates before 2022-04-19.
Could someone please help me understand the reason for these discrepancies in the conversion rate calculations? Do I need to create additional relationship between fact_items[date] and dim_google[date].
Many thanks!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
After reviewing the data model, the problem was traced back to mismatched date formats from two data sources. The fact_items[date] column had a "ddmmyyyy" format, whereas the dim_google[date] column used the "yyyymmdd" format. Attempting to join data based on these mismatched date formats resulted in errors.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
After reviewing the data model, the problem was traced back to mismatched date formats from two data sources. The fact_items[date] column had a "ddmmyyyy" format, whereas the dim_google[date] column used the "yyyymmdd" format. Attempting to join data based on these mismatched date formats resulted in errors.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Helpful resources
User | Count |
---|---|
5 | |
5 | |
3 | |
2 | |
2 |
User | Count |
---|---|
9 | |
7 | |
5 | |
4 | |
4 |