Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Conceptual Question: Data Replacement and Calculated Columns

Hello;

 

I have been working with a large set of data for my finance internship, and I have reached a standstill with my progress. I was hoping that someone with more Power BI skill than I have might be able to help me. Below I have included some information about the way that my project works:

 

Framework:
   • 3 buckets of information: either pulled from Thunder (‘Actuals’ and ‘FCST’) or user generated (‘FAOP’)
   • Each bucket contains different accounts of different departments as rows, each having some form of monetary monthly activity contained in a single column
   • Both department codes and account codes repeat, but the combination of the department and the account is what makes each row unique (i.e. department 123456 may appear multiple times, account 234567 will also appear multiple times, but account 234567 in department 123456 will only appear once). I will refer to these as COAs
   • 3 types of reference tables that do not change: ‘Dept Codes’, ‘Account Codes’, and a ‘Reference Table’ that links both kind of codes and provides more information for each code such as [Account] and [Dept Description].
   • 6 total tables which are all related/flow into each other in some way
Components:
   • ‘Actuals’ and ‘Forecasted’ have the exact same column titles, so they have relationships through the reference tables ‘Department Code’ and ‘Account Code’ due to using the same codes
   • ‘FAOP’ has different column titles from the other two info buckets, but uses the same codes in both ‘Department Code’ and ‘Account Code’ in order to relate to the other info buckets
   • Two calculated columns written into ‘FAOP’ info bucket (not optimal) in order to pull monthly activity from Actuals which allows subtraction from the monthly activity in ‘FAOP’ to find better/worse values.
       o [Actual Sums] uses lookupvalue in ‘Actuals’ for each COA listed in FAOP and returns the monthly value found
       o [B/(W)] subtracts [Actual Sums] from the monthly activity for each COA in ‘FAOP’
Overarching issues:
   • Need a way to drop in new data/replace info in ‘Actuals’, ‘FCST’, and ‘FAOP’ each month without previously entered calculated columns [Actual Sums] and [B/(W)] in ‘FAOP’ being deleted
   • New COAs (data identifiers) can be created daily and do not always line up between ‘FAOP’ and ‘Actuals’, need a way to provide for that
Specific issues:
   • Calculated [B/(W)] column is not pulling in all COAs. Most likely due to some COAs appearing on ‘FAOP’ that don’t appear on ‘Actuals’, and some COAs appearing on ‘Actuals’ that don’t appear on ‘FOAP’.

 

If you need more information or would like to look at my data, please let me know. I would appreciate any help I can get. Thank you!

 

Picture1.png

4 REPLIES 4
tex628
Community Champion
Community Champion

Hello, 

   • New COAs (data identifiers) can be created daily and do not always line up between ‘FAOP’ and ‘Actuals’, need a way to provide for that
Specific issues:
   • Calculated [B/(W)] column is not pulling in all COAs. Most likely due to some COAs appearing on ‘FAOP’ that don’t appear on ‘Actuals’, and some COAs appearing on ‘Actuals’ that don’t appear on ‘FOAP’.

Your unique identifier is appears to be a combination of department ID and account ID. Assuming "Dept Codes" and "Account Codes" hold all possible values for each dimension you should merge these two tables to create a single table holding all possible unique identifiers that can appear in your fact tables. 

With the correct relationship to Actuals, Forecast and FAOP you should catch all of the identifiers.

Overarching issues:
   • Need a way to drop in new data/replace info in ‘Actuals’, ‘FCST’, and ‘FAOP’ each month without previously entered calculated columns [Actual Sums] and [B/(W)] in ‘FAOP’ being deleted

This very much depends on how you load the data. Calculated columns wont be deleted when you add additional data, just recalculated depending on what you add. You can never make a calculated column not calculate, instead you have to tell it how to behave.

For example, if you are adding data by month and you dont want the months to be aggregated together you need to define that the aggregation is to be done by month and not by the entire table.

If you share more details regarding this im sure it can be solved 🙂 

Br,
J




Connect on LinkedIn
Anonymous
Not applicable

Hello @tex628,

 

Thank you for the response! 

 

Your unique identifier is appears to be a combination of department ID and account ID. Assuming "Dept Codes" and "Account Codes" hold all possible values for each dimension you should merge these two tables to create a single table holding all possible unique identifiers that can appear in your fact tables. 


I didn't think to merge the two reference tables... how would you recommend me going about that? My issue is that the calculated columns in 'FAOP' are looking through each row of data within the table, but not every COA in 'FAOP' appears in 'Actuals', and vice versa. Calculated columns can't be calculated for more rows than appear, so how can I get it to calculate for every single COA option? Maybe move the calculated columns to a different table that contains each COA? Not sure how to still be able to lookup data in FAOP/Actuals then...

 

 


This very much depends on how you load the data. Calculated columns wont be deleted when you add additional data, just recalculated depending on what you add. You can never make a calculated column not calculate, instead you have to tell it how to behave.

I understand what you are saying about dropping in new data! Follow up question: when I drop in new data with potential new headers ("June 2019" instead of "June 2018" for example), will it not cause all of my visualizations in my report view to need to be recreated? I will also need to change the calculated column formulas too, correct?

 

Thank you for the help,

 

Kathleen

tex628
Community Champion
Community Champion

I'm assuming that the source of your data are excel files. 

In that case your columns need to have the same headers, if not you will be left with two paralell columns where your calculated column is only calculating one of them:

Jun-18Jul-18
1020
1515
1030


If the column headers are identical the columns will be appended ontop of eachother. This is why i would recommend moving the month into a column in the table instead:

AmountMonth
10Jun-18
15Jun-18
10Jun-18

 

AmountMonth
20Jul-18
15Jul-18
30Jul-18

 
And then append, which would give you:

AmountMonth
10Jun-18
15Jun-18
10Jun-18
20Jul-18
15Jul-18
30Jul-18

Connect on LinkedIn
Anonymous
Not applicable

Overarching issues:
   • Need a way to drop in new data/replace info in ‘Actuals’, ‘FCST’, and ‘FAOP’ each month without previously entered calculated columns [Actual Sums] and [B/(W)] in ‘FAOP’ being deleted. NP: If you want to keep "previously entered calculated columns", you will need to keep the previous data that populated those calculated columns. Perhaps you need some sort of versioning / snapshot for the "FAOP".
   • New COAs (data identifiers) can be created daily and do not always line up between ‘FAOP’ and ‘Actuals’, need a way to provide for that NP: If there is no match between FAOP and Actuals, what is the desired behavior?
Specific issues:
   • Calculated [B/(W)] column is not pulling in all COAs. Most likely due to some COAs appearing on ‘FAOP’ that don’t appear on ‘Actuals’, and some COAs appearing on ‘Actuals’ that don’t appear on ‘FOAP’. NP: If there is no match between FAOP and Actuals, what is the desired behavior?

Cheers,

Nathan

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.