Reply
Regular Visitor
Posts: 32
Registered: ‎03-20-2017

Re: Amounts not showing Correctly

Amounts 2016 is the amounts (sorry to repite myself) of each transaction and by each date of the month and year and will be a YTD

 

the thing is when I insert a table the amounts are ok as SUM, but when i plot chart is not getting the sum amount as total just the last day or the week transactions

Date.PNGGL Accounts-2.PNGGL Accounts-3.PNG

New Contributor
Posts: 658
Registered: ‎07-22-2015

Re: Amounts not showing Correctly

I believe in this example your using Date and FY 2016 tables, correct? On which column these tables are connected?

 

Also can you share the screen shot where you incorrect result on your graph

Regular Visitor
Posts: 32
Registered: ‎03-20-2017

Re: Amounts not showing Correctly

Parry2k, first of all I want to thank you for taking the time to help me and I know that you have many other things to do, Thank you

 

and the screen shot I have already send it before here it is again

Cash.PNG

as you can see these 2 tables are the same information the one on the left is the balance sheet and the one on the right is just filtered by the cash in bank and added the week and the remove the Mapping "condition", the one on the right as you can see the amount of the week 10 is half way to 1M and the table has 12M not even closed, but I figure that is only bringing the value of each week and not adding the values for the whole account

GL Accounts-3.PNG

are these relationships correctly done?

 

thank you

New Contributor
Posts: 658
Registered: ‎07-22-2015

Re: Amounts not showing Correctly

I noticed that you provided the shot in your original post (my mistake), can you answer on what field your FY 2016 and FY 2017 tables are related with date table?

 

If possible, send me excel sheet with sample data  and I will try to look at my end.

 

Regular Visitor
Posts: 32
Registered: ‎03-20-2017

Re: Amounts not showing Correctly

the are connected by the date on the dateSH table and on the FS thru the posting date

 

here is the link of the files

 

thanks

 

 

https://www.dropbox.com/sh/0bfiodh0vaid65l/AAD0OHEpSkttRdrEzT22mazca?dl=0

New Contributor
Posts: 658
Registered: ‎07-22-2015

Re: Amounts not showing Correctly

On thing I noticed that in your table you don't have Week column in it, so basically it is adding up all the Amounts for "Cash in Bank" for all week 10 - 2016" and doing the same for other account, here is quick shot for you:

 

week.PNG

 

Also as a best practice it make sense to combine both "FY" sheet in one table and then use that in PowerBI, let's keep that as a seperate topic.

Super Contributor
Posts: 1,211
Registered: ‎05-10-2016

Re: Amounts not showing Correctly

[ Edited ]

@edavila

 

Hi, do you want YTD of Amount 2016?

 

If answer is yes, i propose do some changes:

 

1. Change relationship between FS2016 and DateSH to Both

 

relationshiptoboth.png

 

2. Create a measure:

 

 

Amount2016YTD =
CALCULATE (
    SUM ( 'FS 2016'[Amount 2016] ),
    FILTER ( ALL ( DateSH ), DateSH[date] <= MAX ( DateSH[date] ) )
)


3. Put this measure in your chart

 

 

Chart.png

 

4. Finally, to order your weeks you should change your WeekSort Column

 

A way to sort is this:

 

Week Sort =
IF (
    DateSH[Week] < 10,
    DateSH[Year] & "-0"
        & DateSH[Week],
    DateSH[Year] & "-"
        & DateSH[Week]
)

 

Like Parry recommend , yo should use only a FS2016-2017 to simplify things later.

Highlighted
Regular Visitor
Posts: 32
Registered: ‎03-20-2017

Re: Amounts not showing Correctly

Victor/Parry

 

thank you and let me ask you another question if I combine both tables what Im going to do when I run out of space in the excel sheet it only has like a 1M rows

Super Contributor
Posts: 1,211
Registered: ‎05-10-2016

Re: Amounts not showing Correctly

[ Edited ]

@edavila

 

You can combine in Power BI Desktop.

 

Import your both tables and go to Edit Query to do this. --Select thr action "Append Queries"

combine.png

 

 

Regular Visitor
Posts: 32
Registered: ‎03-20-2017

Re: Amounts not showing Correctly

Victor

 

to clarify do I need to create a new table and then append both tables and let me tell you that I change the direction of the relationship for FS2016 to both and FS2017 has a single when I try to change it will give and error