measure total - hide blank rows but do not affect total

[fess440]

Hi, I believe similar questions have been tackled before but I have not found workable answers yet. Please help.

I have uploaded a simple example here:

https://www.dropbox.com/sh/crx5heiyhblxsf8/AADVmshkzf11QKfKFveawzQma?dl=0

I use the date slicer to filter the transactions and I need to hide blank rows but I want the total to sum up values since inception.

I'm new to Dax so would appreciate if someone could explain the logic of the calculations.

I'm currently under the impression that calculate(sum([field], all(table[date])) would create a temporary table with all dates and then sum up values, but that's not how it works obviously.

Thanks a lot for your help and time!

[fess440]

any advise anybody, please? i'm pretty stuck. thanks.

[jdbuchanan71 · Accepted solution]

Hello @fess440 

Try adding these two measure.  The first one is just to sum the value in transations so we can us that in the next measure.

Total Value = SUM ( transactions[value] )

The next one checks if it is on a line in the visual where that line is for a singel customer or event and if so returns the [Total Value] measure, the actual filtered amount by date, customer, event.  If not it returns the total of all transactions, the 12286.

Total Measure = 
IF ( ISINSCOPE( customers[name] ) || ISINSCOPE( events[event] ),
    [Total Value],
    CALCULATE(
        [Total Value],
        ALL ( transactions ) 
    )
)

[fess440]

@jdbuchanan71 thanks a lot!

[Anonymous]

@fess440  - 

First, you asked about CALCULATE - using ALL within the second parameter of CALCULATE modifies the "filter context" to ignore any filter on the specified table or column. 

Secondly, could you provide the expected results? I'm not sure what the problem is with the calculation.

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[fess440]

@Anonymous the expected value of the total should be 12,286 at all times regardless of the dates selected in this example. it is the total value when the date slicer is set from 01/01/2019 - 12/05/2020. i also need a solution that will work accordingly for subtotals because this is just an example and i have several hierarchies. hope this is clear.

[Anonymous]

@fess440 - It will work if you add a separate date table and use that date for your slicer, for the value in the table visual, and for the filter in the table visual. Please try that and let us know whether it works.

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[fess440]

@Anonymous i'm not sure if i have understood you correctly but this is what i've done:

1. created date table linked to transaction table

2. changed slicer to have dates from date table instead

3. changed measure to be 

itd = calculate(sumx(transactions, transactions[value]),all('Date'[Date]))

it does not work. do i have to use date table? it changes the min and max of the slicer to the date table, naturally. i would prefer to have the min and max set to the transaction table instead.

can the programming suit the needs please, or do the needs have to change to suit the programming?

thanks.