Help-Really Struggling with My Data

[mWilkinson]

Hi everyone,

i'm struggling to shape my data. 

i've connected with my SQL database and i'm working on financials.

i have one table called "qryFeesAllocations (fees by year)" which is a shaped table of a parent table called "qryFeesAllocations (2)"; and as you probably guessed it, it shows fees received and allocated against invoices generated by my system. so the return looks something like this. 

qryFeesAllocations (fees by Year)

now the problem comes when i want to interact with another table called "qryOffersAndSales (Invoice Paid)" which shows additional sales fees which have been invoiced. 

the issue with my system is that the Fees Allocation table is generated based on the in built accounting system within the software. the OffersAndSales table is populated from within the software but is not included in the in built accounting system. hence why i'm at this problem now. the table below shows the sales table:qryOffersAndSales (Invoice Paid)

what i'd like to do is work out the total (qryFeesAllocations (fees by year) and qryOffersAndSales (Invoice Paid) by year. (side note- i'd also like to do it by month, quarter, week day etc, but my thinking is if i can figure it out on these two shaped tables, with your help, then i can expand it to the other tables)

the column names whose financial values that i need to add together  are as follows:

"qryFeesAllocations (fees by year) [total fee]" and "qryOffersAndSales (Invoice Paid) [SalesFeeincVAT]"

The resulting table should have the following column names:

Fees, SalesFeesincVAT, Total Fee

Rows should be year

Data in table should be as highlighted above. 

ideally, i'd like to look at the percentage change each period as well, however, i feel that that would be another topic.

i've taken a look at forums and tutorials, looked at Merge and Append on the queries, but i can't seem to get my head around the examples given, most likely because i don't understand it fully yet. 

any help would be greatly appreciated and if you need more information please do just ask. 

Step by step instructions would be really helpful as my understanding of PBI is basic/intemediate at best.

[v-piga-msft]

Hi @mWilkinson ,

Have you solved your problem with the suggestion of kcantor ?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share some data smaple which could reproduce your scenario and your desired output so that we could help further on it.

Best  Regards,

Cherry

[mWilkinson]

i'm still working on this. 

the solution doesnt work on my data sets and the mapping that i've created for the overall system. 

really would like step by step instructions on this point. 

[kcantor]

@mWilkinson 

If you need additional help, please supply some sample data, the table and column names from your tables,  and some snips of your data model and relationship connections.  I cannot even begin to provide step by step instructions without more information. We are always willing to help here but simply cannot provide complete answers without detailed information.

[mWilkinson · Accepted solution]

Not to worry. i've figured it out now. 

i had to use measures on the specific tables that i'm working on. 

once i'd created the measures as you'd advised i was able to use the date table and create a new table to show the values i wanted. 

i've now got a complete set of values that show invoices paid for all services and generated graphical images for it. 

next step on this one is to complete a percentage change over time. and then i'm set on this part of the financials and i can move on

[kcantor]

@mWilkinson 

You do not need to append the tables. Your can calculate from different tables and then add the measures.

First Step: Pull in the tables you want to use.

Second Step: Create a simple SUM calculation to total thecolumn in the table.

Format to follow for all columns to sum is Total = SUM(TableName[ColumnName])

     Fees by year = SUM(qryFeesAllocations[fees by year])

     Total Fee = SUM( qryFeesAllocations[total fee])

     Invoice Paid = SUM(qryOffersAndSales[Invoice Paid])

     SalesFeeinCVAT = SUM(qryOffersAndSales[SalesFeeincVAT])

Third Add a date table and connect the data tables to the date table using relationships. Date Table info here: https://community.powerbi.com/t5/Desktop/Creating-Date-Tables/td-p/553980
Fourth: Create a measure to sum your other measures just give a name to this measure and use other measure names in the calculation. Format example: FEES = [Fees by year]+[Total Fee]  Use your discretion on this.

Fifth Create your table. Use your date table to populate rows so drop your year on the row and the value from the measure you created. 

Using the date table, you can also use time intelligence to create totals for previous year to use in your growth %.