Forum Discussion

te271203's avatar
te271203
Regular Visitor
3 months ago
Solved

Tableau to PowerBI Running Totals Issue

Hello all,

 

I have two data sets: one is in a Redshift table and the other is an Excel file containing monthly totals for the table's data set.

 

Attached are screenshots from Tableau and PowerBI.

 

When using the Excel file, the totals are being summed incorrectly, resulting in unusually high values.

 

We tried merging by combining ADMstatus, Plant, lever, and month_key in both the table and the file, but this approach did not work in PowerBI, even though it worked in Tableau with blending.

 

Could you please advise on how to resolve this issue?

 

Tableau:

 

 

Power BI:

 

 

Thank you,

Abdulrasheed.

Need Help
Tips and Tricks
  • te271203's avatar
    te271203
    2 months ago

    Thanks for reaching out. I was able to solve it using a Python script and by adding a few New Data Model tables with joins, which helped me get the cumulative dashboard aligned with the target.

13 Replies

  • danextian's avatar
    danextian
    Super User
    3 months ago

    Hi te271203 

    The simplest approach is to use visual calculation

     

    But the usal DAX pattern is:

    Running Sum Measure = 
CALCULATE (
    [your measure],   -- Base measure to be evaluated (e.g. SUM of sales, revenue, etc.)

    FILTER (
        ALL ( DatesTable ),  
        -- Removes any existing filters on the DatesTable
        -- so the calculation considers the full date range

        DatesTable[Date Column] <= MAX ( DatesTable[DateColumn] )
        -- Keeps only dates up to the current row/context date
        -- MAX(DateColumn) represents the current evaluation date in context
    )
)

     

  • oussamahaimoud's avatar
    oussamahaimoud
    Memorable Member
    3 months ago

    Hi te271203,

    Hope you're doing well!

     

    Here's another way by aggregating before merging. In Power Query, group the Excel file first before merging:

    • In Power Query, select the Excel query.
    • Group By ADMstatus, Plant, lever, month_key → aggregate your target column with Sum
    • Then merge with the Redshift table on those 4 columns

    This replicates Tableau's blend behavior: the secondary source is pre-aggregated before the join.

     

    Hope this helps! Don't forget to accept as solution ✅ and give kudos 👍 in order to keep helping others.

     

    Best regards,

    Oussama (Data Consultant - Expert Fabric & Power BI)

    • te271203's avatar
      te271203
      Regular Visitor
      3 months ago

      Thank you Oussama for your reply. Here is the outcome, i am getting after i have applied with suggestions.

       

       

       

      I see sorting issue and below the logic i am using it for new columns.

       

      Target Running Total =
      VAR CurrentMonth =
          MAX ( 'TEBIT Monthly Summary'[Month Sort] )
      VAR CurrentYear =
          MAX ( 'TEBIT Monthly Summary'[fiscal_year] )
      RETURN
      CALCULATE (
          SUM ( 'TEBIT Monthly Summary'[Targets.Monthly amt] ),
          FILTER (
              ALLSELECTED ( 'TEBIT Monthly Summary' ),
              'TEBIT Monthly Summary'[fiscal_year] = CurrentYear
                  && 'TEBIT Monthly Summary'[Month Sort] <= CurrentMonth
          )
      )
       
      TEBIT Running Total =
      VAR CurrentMonth =
          MAX ( 'TEBIT Monthly Summary'[Month Sort] )
      VAR CurrentYear =
          MAX ( 'TEBIT Monthly Summary'[fiscal_year] )
      RETURN
      CALCULATE (
          SUM ( 'TEBIT Monthly Summary'[Targets.Monthly amt] ),
          FILTER (
              ALLSELECTED ( 'TEBIT Monthly Summary' ),
              'TEBIT Monthly Summary'[fiscal_year] = CurrentYear
                  && 'TEBIT Monthly Summary'[Month Sort] <= CurrentMonth
          )
      )
       
      Please correct me on what went wrong.
       
      Thank you,
      Abdulrasheed.
      • v-karpurapud's avatar
        v-karpurapud
        Community Support
        3 months ago

        Hi te271203 
        Thank you for posting your query in the Microsoft Fabric Community Forum.
         
        The issue is caused because both running total measures are currently summing the same column, and the merge between monthly targets and transaction-level data may also be duplicating values due to granularity mismatch.
         
        Your current measures are referencing the target column in both calculations, so the TEBIT Running Total is also summing the target amount instead of the actual TEBIT values.
         
        Use separate base measures :

        Target Amount =
SUM ( 'TEBIT Monthly Summary'[Targets.Monthly amt] )

TEBIT Amount =
SUM ( 'TEBIT Monthly Summary'[TEBIT amt] )

         
        Then use the running total measures below.

        Target Running Total =
VAR CurrentMonth =
    MAX ( 'TEBIT Monthly Summary'[Month Sort] )
VAR CurrentYear =
    MAX ( 'TEBIT Monthly Summary'[fiscal_year] )
RETURN
CALCULATE (
    [Target Amount],
    FILTER (
        ALLSELECTED ( 'TEBIT Monthly Summary' ),
        'TEBIT Monthly Summary'[fiscal_year] = CurrentYear
            && 'TEBIT Monthly Summary'[Month Sort] <= CurrentMonth
    )
)

---------------------------------------------------------------------
TEBIT Running Total =
VAR CurrentMonth =
    MAX ( 'TEBIT Monthly Summary'[Month Sort] )
VAR CurrentYear =
    MAX ( 'TEBIT Monthly Summary'[fiscal_year] )
RETURN
CALCULATE (
    [TEBIT Amount],
    FILTER (
        ALLSELECTED ( 'TEBIT Monthly Summary' ),
        'TEBIT Monthly Summary'[fiscal_year] = CurrentYear
            && 'TEBIT Monthly Summary'[Month Sort] <= CurrentMonth
    )
)


        Also make sure the Month Name column is sorted using Column Tools → Sort By Column → Month Sort, otherwise Power BI may sort months alphabetically instead of chronologically, which affects the cumulative sequence.
         
        Additionally, since the Excel file contains monthly totals while the Redshift table is transaction-level data, merging them directly can duplicate the monthly target values after the join; to avoid this, either aggregate the Excel file before merging (Group By ADMstatus, Plant, Lever, Month_Key) or keep the target table separate and relate it through dimensions using a star schema approach.

        I hope this helps. If I’ve misunderstood any part of your query, please let us know.

        Regards,

        Microsoft Fabric Community Support Team.