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

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.

     


     

    • v-karpurapud's avatar
      v-karpurapud
      Community Support
      2 months ago

      Hi te271203 

      Just checking in as we haven't received a response to our previous message. Were you able to review the information above? Let us know if you have any additional questions.

      Thank You.

      • te271203's avatar
        te271203
        Regular Visitor
        2 months ago

        Thanks for your concern. Somehow, i couldn't able to do it due to the structure of the data between databrcick table and target excel file without having any key combinations. Is there a way, i can share you the data and try. please assist.

  • oussamahaimoud's avatar
    oussamahaimoud
    Memorable Member
    3 months ago

    Hi te271203,

     

    Use these measures instead of yours :

     

    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'[YOUR_ACTUAL_TEBIT_COLUMN]), -- ← fix this

        FILTER(

            ALLSELECTED('TEBIT Monthly Summary'),

            'TEBIT Monthly Summary'[fiscal_year] = CurrentYear

                && 'TEBIT Monthly Summary'[Month Sort] <= CurrentMonth

        )

    )

     

     

    Replace YOUR_ACTUAL_TEBIT_COLUMN with your real TEBIT/CFI amount column name.

     

    In the Data view, select your month name column → Column Tools → Sort by Column → select Month Sort.

    This ensures October → November → ... → September renders in fiscal order instead of alphabetically.

     

    Please tell me if it works! Don't forget to accept as solution and like my comment in order to keep helping others.

     

    Best regards,

    Oussama (Data Consultant - Expert Fabric & Power BI)