cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Anonymous
Not applicable

Re: Conceptual Question: Data Replacement and Calculated Columns

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

Highlighted
Super User II
Super User II

Re: Conceptual Question: Data Replacement and Calculated Columns

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
Highlighted
Anonymous
Not applicable

Re: Conceptual Question: Data Replacement and Calculated Columns

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

Highlighted
Super User II
Super User II

Re: Conceptual Question: Data Replacement and Calculated Columns

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

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors