I need help getting a ROLL UP SUB TOTALS for a template value

DAX DAX Calculations
1

Hi All,
I have a difficulty in adding a rollup subtotal to my template using the example in Financial reporting. Here is a picture.

image
image756×365 93.1 KB

Cash Position Monitor (Sample).pbix (265.0 KB)

Projection Data List.xlsx (49.2 KB)

2

Additionally, how can I ensure that my calendar dates do not include weekends in the visuals to reduce the number of columns in the tables / matrices ? Thanks

3

@BenBen2,

With regard to your second question, if you’re using @Melissa’s awesome Extended Date Table, this is a snap. You can just create a one column table of working dates via:

Working Dates = 
CALCULATETABLE(
    VALUES( Dates[Date] ),
    FILTER(
        Dates,
        Dates[IsWorkingDay] = TRUE()
    )
)
  • Brian
1 Like
4

Appreciated Brian,
I will try it. Hopefully you or some other expert can help with the first question too…
Thanks

5

That’s one of the few DAX functions I’ve never used or even played around with, so I’ll rely on someone else to field that one…

  • Brian
1 Like
6

So I have tried the DAX formula below, but it still gives me the same picture and no Roll up totals. Can anyone figure what I am doing wrong?

OneValueCheck = IF( HASONEVALUE( 'Projection Table'[Summary]),
                    CALCULATE(SUMX('Projection Table', [Balance]),
                        DATESMTD(Dates[Date])), BLANK())
7

@BenBen2 Have you tried building a hierarchy where you make the subtotal groups a dimension of the different GL References? So rather than a separate Index number for Total Savings (for example), you make the value in the Summary field for Alloya and PNC Total Savings and then build a hierarchy where Summary is at the top and GL Titles are below? Then your users could expand or contract based on whether they want to see only the rollups or if they want to drill down.

8

Hi Tim,
That in fact is my trajectory. However, I cannot proceed as my skills hit a dead end. Would you be able to provide some insights?

9

Hi Tim,
I was reading your post, and I think it may lead me to the solution. Would you please clarify what you mean by making the value in the summary field for Alloya for example?