Total next to year to date value

[Plantje]

Hi,

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

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

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!

[quentin_vigne]

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]

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, 😎

GO

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

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]

@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]

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]

@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]

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]

Accountnumber Fiscal year Fiscal month Book date Actuals Budget

123	2018	1	1-1-2018 00:00:00	12	10
123	2018	2	1-2-2018 00:00:00	12	10
123	2018	3	1-3-2018 00:00:00	12	10
123	2017	12	1-12-2017 00:00:00	14	11
123	2017	11	1-11-2017 00:00:00	14	11
456	2018	1	1-1-2018 00:00:00	2	4
456	2018	2	1-2-2018 00:00:00	3	5
456	2018	3	1-3-2018 00:00:00	2	4
456	2018	4	1-4-2018 00:00:00	2	4
123	2017	1	1-1-2017 00:00:00	2	3
123	2017	2	1-2-2017 00:00:00	2	4
123	2017	3	1-3-2017 00:00:00	7	8

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:00	1	1	1-1-2018 00:00:00	januari	1	1	2	1	2018	1-1-2018 00:00:00	20180101	01/01/2018
2-1-2018 00:00:00	2	1	1-1-2018 00:00:00	januari	1	1	3	1	2018	1-1-2018 00:00:00	20180102	01/02/2018
3-1-2018 00:00:00	3	1	1-1-2018 00:00:00	januari	1	1	4	1	2018	1-1-2018 00:00:00	20180103	01/03/2018
4-1-2018 00:00:00	4	1	1-1-2018 00:00:00	januari	1	1	5	1	2018	1-1-2018 00:00:00	20180104	01/04/2018
5-1-2018 00:00:00	5	1	1-1-2018 00:00:00	januari	1	1	6	1	2018	1-1-2018 00:00:00	20180105	01/05/2018
6-1-2018 00:00:00	6	1	1-1-2018 00:00:00	januari	1	1	7	1	2018	1-1-2018 00:00:00	20180106	01/06/2018
7-1-2018 00:00:00	7	1	1-1-2018 00:00:00	januari	2	1	1	1	2018	1-1-2018 00:00:00	20180107	01/07/2018
8-1-2018 00:00:00	8	1	1-1-2018 00:00:00	januari	2	2	2	1	2018	1-1-2018 00:00:00	20180108	01/08/2018
9-1-2018 00:00:00	9	1	1-1-2018 00:00:00	januari	2	2	3	1	2018	1-1-2018 00:00:00	20180109	01/09/2018

[Plantje]

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

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

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 select month 2 it would look something like this:

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

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 · Accepted solution]

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

[Plantje]

Cool! Thanks!

This does the trick! I think for the actual report I want to add every field that I want to group by in the same way as Table1[AccountNumber] is added here, right?

The only thing that I needed to change is that I should not use [Fiscal year] from the main query, but [Jaar] from the time dimension that I joined to.

Next to that I will also look into the "ALLEXCEPT" function. I did already play around with SUM combined with CALCULATE, but didn't find this.

[quentin_vigne]

@Plantje

Yes that's it !

Yes sorry, I've used fiscal year because your time table was not complete so I was not finding the same result as you did.

You're welcome

- Quentin