Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Community Support
Community Support

Hi @Anonymous ,

 

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Anonymous
Not applicable

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. 

Anonymous
Not applicable

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.
 



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.