Forum Discussion

masplin's avatar
masplin
Impactful Individual
6 years ago
Solved

Foxed why ALL calculation not working

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...
Need Help
  • v-joesh-msft's avatar
    v-joesh-msft
    6 years ago

    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/EQA5J8f9iMVMlgUe0dsy3uoBmrJj57oDfztwqyabGBF6Ig?e=R9z5b1

    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's avatar
masplin
Impactful Individual
6 years ago

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's avatar
v-joesh-msft
Solution Sage
6 years ago

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.