Reply
FinancialAdviso
Regular Visitor

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:

  1. Date[Date] has a 1-many relationship with fact_items[date]
  2. Date[Date] has a 1-many relationship with dim_google[date]
  3. 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:

  1. When I evaluate the two measures ([Items_Purchased] and [Add_to_Cart]) separately, they provide correct values for whole periods.
  2. 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.
  3. I also noticed that I'm not able to see the conversion rate for dates before 2022-04-19.

 

FinancialAdvise_1-1693674896845.png

 

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!

 

1 ACCEPTED SOLUTION
FinancialAdviso
Regular Visitor

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.

View solution in original post

2 REPLIES 2
FinancialAdviso
Regular Visitor

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.

lbendlin
Super User
Super User

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...

avatar user

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.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)