Total row not showing no matter what I do!

I know this must be simple and it’s a variation on a theme that many people have raised but the answers don’t seem to help me.

Now, the task is to create a forecast.

Give me the actuals for the months where there are actuals +
ADD the forecast ONLY for the months where there are no actuals.


I have 2 key measures. One calculates actuals based on a lot of filters. It uses the key measure [sum[actual] (actual is column in the fact table)

Measure 2 is the same measure but uses a different key measure [sum[budget]. (budget is another column in the fact table)

Should be simple, shouldn’t it?
I can get the figures for each based on a max date variable.
BUT, no matter what I try, I cannot get totals.

I know it’s to do with no filter context, but WHAT to do, escapes me. I have tried sumx and values but I get ALL the values for budget figures.

Any and all help would be mega appreciated.

@Ericadyson,

From the description alone, the problem could be a lot of different things. However, if you can please post your PBIX file, I think this will be pretty straightforward to diagnose and fix.

Thanks.

  • Brian

Thanks Brian. I will edit the file as best I can so that the Hebrew isn’t a barrier!

Actually the 2 key field names are in English (actual and budget)… so that’s a helpful start. Will get back to you. Thanks again.

Hi Erica,

Can you give this a go…

Budget ENG =
VAR LastActual = CALCULATE( MAX( 'Date'[Date] ), FILTER( ALL('Date'), [EXP actual-] <>0), ALLSELECTED( 'Date' ))
VAR FCperiods = CALCULATETABLE( VALUES( 'Date'[YYMM] ), ALLSELECTED( 'Date' ), 'Date'[Date] >= EDATE( LastActual, 1 ))
RETURN

SUMX( FCperiods, [EXP budget] ) + [EXP actual-]

.
I changed your Date Hierarchy to make sense of it all (see field names shown below)… but I think you can follow along.

First determined what the last Actuals date is since you calculated in a monthly granularity. Next I created a virtual table with all YYMM values in the ‘visible’ date range using EDATE in combination with ALLSELECTED - to identify where the YYMM is equal or greater than the LastActual Month +1.

All that’s left is to iterate over that virtual table within the table this results in a single value because of the context on the rows but for the Total it will iterate over all those 5 months to sum up the Budget

I hope this is helpful.

2 Likes

Hi Melissa

Wonderful. I knew I had to create a virtual table and use sumx to get the total, but I was iterating over the fact table and not the dates table! I didn’t know about EDATE… so that was missing also.

A big thank you for helping me out on this. For this job, I have about 10 different types of expenditure and I have a multi-filtered expression for each type. And then I create a total expenditure expression. And the same for income. So the DAX you’ve helped me with will be put to use many times over!

Re the date hierarchy: I didn’t actually bother too much which set of dates I dragged into the visual… but yes, the reports are all at the month. or quarter or year level.

I would NEVER, have got to this on my own. So am mega thankful and appreciative of your time, knowledge, and timeliness of replies! In short, thanks a lot! Erica

Glad I could help.
:+1:

It’s great to know that you are making progress with your query @Melissa. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!