Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
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.
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
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.
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
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.
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 |
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"
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |