Forum Discussion
Tableau to PowerBI Running Totals Issue
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.
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.
Community SupportHi 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
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.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.
- v-karpurapud
Hi te271203
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Include all the necessary details, detailing your scenario and issue as clearly and fully as possible.
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI…
Best regards,
Community Support Team.