Forum Discussion
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
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:
Power BI Version
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.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.
3 Replies
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.
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.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.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.