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

How do you create a "Total" only using specific columns in a matrix?

Hi all,

I'm creating a financial report where we need to see the actuals per quarter and using a forcasted quarter (A measure I used to pull the prior quarter total

"Forecast = CALCULATE(SUM(Actual[Actual Amount]), PREVIOUSQUARTER(Dates[Date]))" ). 


What I'm trying to do is sum the first 3 quarters with the 4th quarter forecasted value as a total.  The old excel report just did a sum of the 3 quarters plus the forecasted value (that was just was copy of the prior quarter). But I'm confused on how to go about this in DAX. The current total in PBI is based upon all 4 quarter ytd (That is not going to work). 

Excel Version:
excel budget forecast.PNG

Power BI Version

budget forecast.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
RDLOPEZ Regular Visitor
Regular Visitor

Re: How do you create a "Total" only using specific columns in a matrix?

So for now I've come up with a work around but sadly this wont work dynamically. But it is giving me the values I need for the report, I'll just need to update the measures for each quarter. 

• First I did a sum with hard dates for the first 3 quarters:

First 3 quarters = CALCULATE(SUM(Actual[Actual Amount]), DATESBETWEEN(Dates[Date],DATE(2019,1,1),DATE(2019,9,1)))

• Then another measure to only show the quarter 3 total (To replicate the 3rd quarter total to show the forcasted quarter total):
Quarter 3 = CALCULATE(SUM(Actual[Actual Amount]),DATESBETWEEN(Dates[Date],DATE(2019,7,1),DATE(2019,9,1)))

Then I summed those two measures:
Forecasted Total = CALCULATE([Quarter 3]+[first 3 quarters])

I was then able to do variance calculations between the forcasted 2019 actuals vs the 2020 buget.

Report Image.PNG
 
So for now I have resolved my issue, but if anyone knows a way I can do the same but make it so I no longer need to manually change the hard date ranges and make it work off the date slicers choices I would be forever greatful.
 



View solution in original post

3 REPLIES 3
v-jayw-msft Senior Member
Senior Member

Re: How do you create a "Total" only using specific columns in a matrix?

Hi @RDLOPEZ ,

 

Could you please share some sample data to me if you don't have any Confidential Information?

I'm not quiet sure about your table structure, some sample data might make me more effective in helping you.

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

RDLOPEZ Regular Visitor
Regular Visitor

Re: How do you create a "Total" only using specific columns in a matrix?

Not sure how I can show sample data seeing as this is part of a much bigger report. Plus I have about 20 measures. That and this is all pretty confidential info. The matrix table I showed has two rows (Department and Dept Manager) columns are dates.quarter and values have 8 different fields (But I'm only showing Actuals and the forecast measure) I had to do this to show all departments even if they didn't have a budget or anything spent the year they are researching. 

But to simplify this a bit more what you are seeing is the value portion of the matrix table (Had to hide the rows due to confidentiality) The "CY" values come from the Actuals Field, the forecasted value is from a PreviousQuarter measure. I just need to sum these to create a total. 

RDLOPEZ Regular Visitor
Regular Visitor

Re: How do you create a "Total" only using specific columns in a matrix?

So for now I've come up with a work around but sadly this wont work dynamically. But it is giving me the values I need for the report, I'll just need to update the measures for each quarter. 

• First I did a sum with hard dates for the first 3 quarters:

First 3 quarters = CALCULATE(SUM(Actual[Actual Amount]), DATESBETWEEN(Dates[Date],DATE(2019,1,1),DATE(2019,9,1)))

• Then another measure to only show the quarter 3 total (To replicate the 3rd quarter total to show the forcasted quarter total):
Quarter 3 = CALCULATE(SUM(Actual[Actual Amount]),DATESBETWEEN(Dates[Date],DATE(2019,7,1),DATE(2019,9,1)))

Then I summed those two measures:
Forecasted Total = CALCULATE([Quarter 3]+[first 3 quarters])

I was then able to do variance calculations between the forcasted 2019 actuals vs the 2020 buget.

Report Image.PNG
 
So for now I have resolved my issue, but if anyone knows a way I can do the same but make it so I no longer need to manually change the hard date ranges and make it work off the date slicers choices I would be forever greatful.
 



View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)