Custom Total when drilled up

DAX DAX Calculations
1

image
image754×344 8.09 KB

Prov stock 20_10.pbix (172.3 KB)

In the above file, I need the balance at last month of the year to be displayed as" year total " when drilled up to the year

image

In this case for 2019, it should be 9 instead of 36, and for 2020 should be 5 instead of 17.

2

Hi @Anu,

Not sure this is possible but I hope someone will prove me wrong …

Let’s say we created a measure like this:

Closing Balance = 
VAR LastDates = 
    SELECTCOLUMNS(
        ADDCOLUMNS(
            CALCULATETABLE( VALUES( 'Date'[Year] ), ALLSELECTED( 'Date'[Date] )),
            "@MaxDate", CALCULATE( MAX( Funding[Date] ), VALUES( Funding[Date] ))
        ), "LastDates", [@MaxDate]
    )
RETURN

IF( ISINSCOPE( 'Date'[MonthName] ),
    SUM( Funding[Closing Balance] ),
    CALCULATE( SUM( Funding[Closing Balance] ), FILTER( ALL( 'Date'[Date] ), 'Date'[Date] IN LastDates ))
)

.

When drilled up the result is as below, looking good - right
image

But when you drill back down again you get the same Total and that’s because you can’t actually perform a test to see if Months are being displayed in a visual or not (as far as I know at this time) and that “Month” context is missing in the Total field.

image
image821×239 14.4 KB

So I think your only alternative is to layer the visuals and switch between them (show/hide) using buttons.

1 Like
3

Could you please attach the sample file also

image
image727×247 46.3 KB

I am fine with 9 and 5 coming as Total at month level.

4

Hi @Anu,

Here’s the file.
Prov stock 20_10.pbix (173.3 KB)

5

Also why did you use ISINSCOPE and not HASONEVALUE

6

Hi @Anu,

If there is filtering coming from a Slicer HASONEVALUE for MonthName would return TRUE. But if MonthName is not part of the filter in the matrix (like for the totals ), ISINSCOPE returns FALSE.

You can read up on it here:

I hope this is helpful.

7

image

In This, is there a way to display 5 instead of 14.
Main concept is it , Closing Balance across dates should not be added up.
What ever is the latest should be displayed as Total
In this case Total of 9 for 2019 and Total of 5 for 2020 is fine.
The net total should also be 5 and not 14.

8

Hi @Anu,

Sure you can just refer to the MAX date, like below.

Closing Balance v2 = 
VAR MaxDate = CALCULATE( MAX( Funding[Date] ), VALUES( Funding[Date] ))
RETURN

IF( ISINSCOPE( 'Date'[MonthName] ),
    SUM( Funding[Closing Balance] ),
    CALCULATE( SUM( Funding[Closing Balance] ),   FILTER( ALL( 'Date'[Date] ), 'Date'[Date] = MaxDate))
)

.

with this result.

image
image835×293 18 KB

Here’s your updated sample file. Prov stock 20_10.pbix (173.4 KB)
I hope this is helpful.

9

But I tried using HASONEVALUE in place of ISINSCOPE in your formula, it is returning same results?

10

Yes as long as there is no external filter…

please see details in post #6

11

Is this enough,

Closing Balance v3 =
IF( HASONEVALUE( ‘Date’[MonthName] ),
SUM( Funding[Closing Balance] ),
CALCULATE( SUM( Funding[Closing Balance] ), FILTER( ‘Date’, ‘Date’[Date] = MAX(Funding[Date]))
))