cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Plantje Regular Visitor
Regular Visitor

Total next to year to date value

Hi,

 

I have a pretty straight forward data set that I created for test purposes: data set.JPG

 

With that I figured out how to get a nice year to date:

Year to date.JPG

Now I would like to have two additional columns with the totals. So a "Actuals Total" and a "Budget Total"

 

In this example I would expect to see something like:

Jaar  || Accountnumber || Actuals || Budget || YTD_Actuals || YTD_Budget || Actuals Total || Budget Total

2017 || 123                    ||  7          || 8           || 11                  || 15               || 39                 || 37

2018 || 123                    ||  12        || 10         || 36                  || 30               || 36                 || 30

2018 || 456                    ||  2          || 4           || 7                   || 13                || 9                   || 17

 

And if I were to select month 2 I would expect to see:

Jaar  || Accountnumber || Actuals || Budget || YTD_Actuals || YTD_Budget || Actuals Total || Budget Total

2017 || 123                    || 2           || 4           || 4                   || 7                 || 39                 || 37

2018 || 123                    || 12         || 10         || 24                 || 20               || 36                 || 30

2018 || 456                    ||  2          || 4           || 5                   || 9                 || 9                   || 17

 

 

The Actuals and Budget columns are not that important; just the YTD_Actuals, YTD_Budget, Actuals Total and Budget Total.

 

I don't know why this is so hard, but I cannot get it done.

 

Obviously this is just test data. In the actual data I have way more account numbers and I also have more levels like the category, subcategory and revenue/cost indication. But I would expect that is more like this.

 

Hope this is a piece of cake for some of you out there! Thanks!

 

1 ACCEPTED SOLUTION

Accepted Solutions
quentin_vigne Senior Member
Senior Member

Re: Total next to year to date value

Hi @Plantje

 

It took times for me to understand but here it is : 

 

 

Actuals Total = CALCULATE(SUM(Table1[Actuals]);ALLEXCEPT(Table1;Table1[AccountNumber];Table1[Fiscal year]))

 

Budget Total = CALCULATE(SUM(Table1[Budget]);ALLEXCEPT(Table1;Table1[AccountNumber];Table1[Fiscal year]))

 

Maybe you will have to replace ; by , (depend of countries)

 

- Quentin

11 REPLIES 11
quentin_vigne Senior Member
Senior Member

Re: Total next to year to date value

Hi @Plantje

Maybe you can post here a sample of your data ? (As a table / something we can copy) ( cf : https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 )

 

 

Else, what's your calculation for Actuals Total and Budget Total ? 

 

- Quentin

Plantje Regular Visitor
Regular Visitor

Re: Total next to year to date value

Thank you for your response!

 

The script to create the table and the data:

USE [Reporting]

GO

/****** Object:  Table [dbo].[Finance_test]    Script Date: 29-5-2018 14:56:20 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Finance_test](

       [Accountnumber] [varchar](9) NULL,

       [Fiscal year] [smallint] NULL,

       [Fiscal month] [varchar](3) NULL,

       [Book date] [date] NULL,

       [Actuals] [float] NULL,

       [Budget] [float] NULL

) ON [PRIMARY]

GO

INSERT [dbo].[Finance_test] ([Accountnumber], [Fiscal year], [Fiscal month], [Book date], [Actuals], [Budget]) VALUES (N'123', 2018, N'1', CAST(N'2018-01-01' AS Date), 12, 10)

GO

INSERT [dbo].[Finance_test] ([Accountnumber], [Fiscal year], [Fiscal month], [Book date], [Actuals], [Budget]) VALUES (N'123', 2018, N'2', CAST(N'2018-02-01' AS Date), 12, 10)

GO

INSERT [dbo].[Finance_test] ([Accountnumber], [Fiscal year], [Fiscal month], [Book date], [Actuals], [Budget]) VALUES (N'123', 2018, N'3', CAST(N'2018-03-01' AS Date), 12, 10)

GO

INSERT [dbo].[Finance_test] ([Accountnumber], [Fiscal year], [Fiscal month], [Book date], [Actuals], [Budget]) VALUES (N'123', 2017, N'12', CAST(N'2017-12-01' AS Date), 14, 11)

GO

INSERT [dbo].[Finance_test] ([Accountnumber], [Fiscal year], [Fiscal month], [Book date], [Actuals], [Budget]) VALUES (N'123', 2017, N'11', CAST(N'2017-11-01' AS Date), 14, 11)

GO

INSERT [dbo].[Finance_test] ([Accountnumber], [Fiscal year], [Fiscal month], [Book date], [Actuals], [Budget]) VALUES (N'456', 2018, N'1', CAST(N'2018-01-01' AS Date), 2, 4)

GO

INSERT [dbo].[Finance_test] ([Accountnumber], [Fiscal year], [Fiscal month], [Book date], [Actuals], [Budget]) VALUES (N'456', 2018, N'2', CAST(N'2018-02-01' AS Date), 3, 5)

GO

INSERT [dbo].[Finance_test] ([Accountnumber], [Fiscal year], [Fiscal month], [Book date], [Actuals], [Budget]) VALUES (N'456', 2018, N'3', CAST(N'2018-03-01' AS Date), 2, 4)

GO

INSERT [dbo].[Finance_test] ([Accountnumber], [Fiscal year], [Fiscal month], [Book date], [Actuals], [Budget]) VALUES (N'456', 2018, N'4', CAST(N'2018-04-01' AS Date), 2, 4)

GO

INSERT [dbo].[Finance_test] ([Accountnumber], [Fiscal year], [Fiscal month], [Book date], [Actuals], [Budget]) VALUES (N'123', 2017, N'1', CAST(N'2017-01-01' AS Date), 2, 3)

GO

INSERT [dbo].[Finance_test] ([Accountnumber], [Fiscal year], [Fiscal month], [Book date], [Actuals], [Budget]) VALUES (N'123', 2017, N'2', CAST(N'2017-02-01' AS Date), 2, 4)

GO

INSERT [dbo].[Finance_test] ([Accountnumber], [Fiscal year], [Fiscal month], [Book date], [Actuals], [Budget]) VALUES (N'123', 2017, N'3', CAST(N'2017-03-01' AS Date), 7, 8)

GO

 

As far as the report goes (where I joined Query2 and Query1 on datum -- Book date; Query2 is my time dimension)

Report.JPG

 

With regards to the Actuals Total and Budget Total: I have no calculation for that. That's exactly what I cannot figure out.

quentin_vigne Senior Member
Senior Member

Re: Total next to year to date value

@Plantje

 

What do you mean when you say you can't figure it out ? You don't know where it come from ?

 

plus I can't create a table in PowerBI in that way, I need you to copy and paste the data here and not the query

Thanks

 

- Quentin

Plantje Regular Visitor
Regular Visitor

Re: Total next to year to date value

Ah okay, I'm sorry... I'm using a SQL server source. I'll try to paste the data as a table. I'll read through the link that you gave once more. I will give that proposed way a try as soon as I am back at my desk.

 

What I cannot figure out is how to get the values Budget Total and Actuals Total as I described in my example.

quentin_vigne Senior Member
Senior Member

Re: Total next to year to date value

@Plantje

 

Where did your example come from ? 

If you don't know the calculation for these columns it makes the thing a little bit harder

 

- Quentin

Plantje Regular Visitor
Regular Visitor

Re: Total next to year to date value

I have calculated the example myself. The data shown in the screen shots is all the data that I worked with for reproducing the issue I am running into. The example is as easy as summing the values that I need. The only thing is: I don't know how to get Power BI to do that.

Plantje Regular Visitor
Regular Visitor

Re: Total next to year to date value

Accountnumber Fiscal year Fiscal month Book date Actuals Budget

123201811-1-2018 00:00:001210
123201821-2-2018 00:00:001210
123201831-3-2018 00:00:001210
1232017121-12-2017 00:00:001411
1232017111-11-2017 00:00:001411
456201811-1-2018 00:00:0024
456201821-2-2018 00:00:0035
456201831-3-2018 00:00:0024
456201841-4-2018 00:00:0024
123201711-1-2017 00:00:0023
123201721-2-2017 00:00:0024
123201731-3-2017 00:00:0078

That's the main data.

 

And this is part of my time dimension:

Datum Dag Maand Eerste dag van maand Maandnaam Week ISOweek Dag van week Kwartaal Jaar Eerste dag van jaar Style112 Style101

1-1-2018 00:00:00111-1-2018 00:00:00januari112120181-1-2018 00:00:002018010101/01/2018
2-1-2018 00:00:00211-1-2018 00:00:00januari113120181-1-2018 00:00:002018010201/02/2018
3-1-2018 00:00:00311-1-2018 00:00:00januari114120181-1-2018 00:00:002018010301/03/2018
4-1-2018 00:00:00411-1-2018 00:00:00januari115120181-1-2018 00:00:002018010401/04/2018
5-1-2018 00:00:00511-1-2018 00:00:00januari116120181-1-2018 00:00:002018010501/05/2018
6-1-2018 00:00:00611-1-2018 00:00:00januari117120181-1-2018 00:00:002018010601/06/2018
7-1-2018 00:00:00711-1-2018 00:00:00januari211120181-1-2018 00:00:002018010701/07/2018
8-1-2018 00:00:00811-1-2018 00:00:00januari222120181-1-2018 00:00:002018010801/08/2018
9-1-2018 00:00:00911-1-2018 00:00:00januari223120181-1-2018 00:00:002018010901/09/2018
Plantje Regular Visitor
Regular Visitor

Re: Total next to year to date value

I also made my example result copy-paste-able:

So, in the example that I provided first I would expect something like this:

JaarAccountnumberActualsBudgetYTD_ActualsYTD_BudgetActuals TotalBudget Total
20171237811153937
2018123121036303630
201845624713917

 

And if I select month 2 it would look something like this:

JaarAccountnumberActualsBudgetYTD_ActualsYTD_BudgetActuals TotalBudget Total
201712324473937
2018123121024203630
20184562459917

 

Please note: I currently have most fields available and most values displayed here come from the working report.

Needed: the calculation to create the fields "Actuals Total" and "Budget Total"

quentin_vigne Senior Member
Senior Member

Re: Total next to year to date value

Hi @Plantje

 

It took times for me to understand but here it is : 

 

 

Actuals Total = CALCULATE(SUM(Table1[Actuals]);ALLEXCEPT(Table1;Table1[AccountNumber];Table1[Fiscal year]))

 

Budget Total = CALCULATE(SUM(Table1[Budget]);ALLEXCEPT(Table1;Table1[AccountNumber];Table1[Fiscal year]))

 

Maybe you will have to replace ; by , (depend of countries)

 

- Quentin