Forum Discussion

GanesaMoorthyGM's avatar
GanesaMoorthyGM
Icon for Helper II rankHelper II
1 year ago
Solved

Unusual Data Mismatch

Hii Guys,

So Today I was working in Sales and Target report and Unusual mismatch occurs, let me explain
So i have Cluster Dim which models with fact sales and Cluster Target

Cluster DIM has
Cluster ID
Cluster

here Cluster is mapped with fact_sales Product Cluster and Cluster Target Product Cluster

the mismatch is for Gold_Coins in Oman alone the value mismatch occurs in power bi table visual it shows 0.25 but the actual in excel is 0.39


I'll be using Cluster Dim only for sales and Target data comparison and the relationship is also fine i guess but i dont know why this occurs kindly help me fixing this.
Thanks in advance

Need Help
Show and Tell
Tips and Tricks
  • v-achippa's avatar
    v-achippa
    1 year ago

    Hi GanesaMoorthyGM,

     

    Is you issue resolved? If not please follow the below approach.

     

    Here the mismatch happens because your current measure calculates the last sales date in a cluster filtered context. So in the cluster dim visual this cuts off early, so you only see 0.25.
    To fix it, please use the below measure:

     

    Oman YTD Target =
    VAR LastSalesDate = CALCULATE(
    MAX('Fact_Sales_Master'[DateOnly]),
    ALL('Fact_Sales_Master'),
    'Fact_Sales_Master'[Country] = "Oman"
    )

     

    This way both visuals will consistently return 0.39 for Gold_Coins, matching your excel result.

     

    Thanks and regards,

    Anjan Kumar Chippa

9 Replies

  • GanesaMoorthyGM's avatar
    GanesaMoorthyGM
    Icon for Helper II rankHelper II
    1 year ago

    Thanks for uour quick response jaineshp ,

    I already tried this

    1. Double-check if Gold_Coins exists with duplicates or small spelling/space differences across DIM and Target.

    2. Make sure the relationship between Cluster DIM → Target is 1-to-many, single direction.
      But still not fixed

    • v-achippa's avatar
      v-achippa
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi GanesaMoorthyGM,

       

      Thank you for reaching out to Microsoft Fabric Community.

       

      Thank you jaineshp, Shahid12523 and danextian for the prompt response. 

       

      As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

       

      Thanks and regards,

      Anjan Kumar Chippa

  • Shahid12523's avatar
    Shahid12523
    Icon for Community Champion rankCommunity Champion
    1 year ago

    The mismatch (0.25 in Power BI vs 0.39 in Excel for Gold_Coins in Oman) is likely due to one of these:
    - Filter context: Power BI might be filtering Oman differently.
    - Stale data: Excel was updated but Power BI wasn’t refreshed.
    - Mapping issue: Gold_Coins might not be mapped correctly in Cluster DIM.
    - DAX logic: Your measure might exclude some rows unintentionally.
    Start by checking filters, refreshing data, and validating mappings.

  • GanesaMoorthyGM's avatar
    GanesaMoorthyGM
    Icon for Helper II rankHelper II
    1 year ago

    So guys I was still not able to sort this out,
    I'll add additional detail and screenshot 0.25 it doesnt include Target of June,July,August
    whereas 0.39 includes upto August from april but 0.25 is the target value of April and May alone


    But the measure that i used is same for these 2 visuals

    Oman YTD Target =
    VAR LastSalesDate = CALCULATE(
        MAX('Fact_Sales_Master'[DateOnly]),
        FILTER(
            'Fact_Sales_Master',
            'Fact_Sales_Master'[Country] = "Oman"
        )
    )

    VAR CurrentYear = YEAR(LastSalesDate)
    VAR CurrentMonth = MONTH(LastSalesDate)

    -- Determine the fiscal year start based on last available sales date
    VAR FiscalYearStartYear = IF(CurrentMonth >= 4, CurrentYear, CurrentYear - 1)

    RETURN
    COALESCE(
        CALCULATE(
            SUM('Cluster Target'[Target]),
            FILTER(
                'Cluster Target',
                'Cluster Target'[Country] = "Oman" &&
                VAR MonthNumber = MONTH(DATEVALUE("01 " & 'Cluster Target'[Month] & " 2000"))
                VAR MonthYear = IF(MonthNumber >= 4, FiscalYearStartYear, FiscalYearStartYear + 1)
                VAR TargetDate = DATE(MonthYear, MonthNumber, 1)
                RETURN
                    TargetDate >= DATE(FiscalYearStartYear, 4, 1) &&
                    TargetDate <= DATE(CurrentYear, CurrentMonth, 1)
            )
        ),
        0
    )
    this is my measure





    • v-achippa's avatar
      v-achippa
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi GanesaMoorthyGM,

       

      Is you issue resolved? If not please follow the below approach.

       

      Here the mismatch happens because your current measure calculates the last sales date in a cluster filtered context. So in the cluster dim visual this cuts off early, so you only see 0.25.
      To fix it, please use the below measure:

       

      Oman YTD Target =
      VAR LastSalesDate = CALCULATE(
      MAX('Fact_Sales_Master'[DateOnly]),
      ALL('Fact_Sales_Master'),
      'Fact_Sales_Master'[Country] = "Oman"
      )

       

      This way both visuals will consistently return 0.39 for Gold_Coins, matching your excel result.

       

      Thanks and regards,

      Anjan Kumar Chippa

      • v-achippa's avatar
        v-achippa
        Icon for Community Support rankCommunity Support
        11 months ago

        Hi @GanesaMoorthyGM,

         

        We wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.

         

        Thanks and regards,

        Anjan Kumar Chippa