Foxed why ALL calculation not working

[masplin]

I'm struggling ot understand why this code is not filling in the blanks in this matrix. I was expecting on the row 7:00 it would add up the amounts for both dates which = 6 and put 6 in the Measure 2 value for both dates? 

Measure 2 = 
VAR TableVar =
    ADDCOLUMNS (
        SUMMARIZE ( ATEs, 'Time'[TimeKey],'Date'[Date] ),
        "ATEs", ATEs[ATE Count]
            
    )
RETURN
CALCULATE(
   SUMX(TableVar,  [ATEs]),ALL('Date'))

The ATE table looks like this. TimeKey and Date are just standard Date and Time master tables. [ATE Count] is just a countrows on ATE table. 

Any clues appreciated

Mike

[VijayP]

Some times We all miss some basic thing. Please check the relation ship of all tables is it intact?

[masplin]

yep or the first column wouldn't have worked

[v-joesh-msft · Accepted solution]

Hi @masplin ,

I don't know if it is the result you want. You need to create the following date table.

Date = CALENDAR(MIN('ATEs'[ATEDate]),MAX('ATEs'[ATEDate]))  

Then create the following measure:

Measure 2 =
VAR TableVar =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( ATEs, 'Time'[TimeKey], 'Date'[Date] ),
            "ATEs", ATEs[ATE Count]
        ),
        ALL ( 'Date' )
    )
RETURN
    CALCULATE ( CALCULATE ( SUMX ( TableVar, [ATEs] ), ALL ( 'Date' ) ) )

Results are as follows:

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EQA5J8f9iMVMlgUe0d...

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[masplin]

Ah is it that in my measure my TableVAR is picking up the context of the column/row of each cell i.e. a single date and time so there  is nothing to add up. You VAR measure is creating a table for the whole set of dates for each time?

[v-joesh-msft]

Hi @masplin ,

Yes, you are right, in fact, the formula can be simplified to this:

Measure 2 =
VAR TableVar =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( ATEs, 'Time'[TimeKey], 'Date'[Date] ),
            "ATEs", ATEs[ATE Count]
        ),
        ALL ( 'Date' )
    )
RETURN
    SUMX ( TableVar, [ATEs] )

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.