Forum Discussion

masplin's avatar
masplin
Icon for Impactful Individual rankImpactful 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.

VijayP's avatar
VijayP
Icon for Community Champion rankCommunity Champion
6 years ago

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

masplin's avatar
masplin
Icon for Impactful Individual rankImpactful Individual
6 years ago

yep or the first column wouldn't have worked

 

  • v-joesh-msft's avatar
    v-joesh-msft
    Icon for Solution Sage rankSolution Sage
    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
      Icon for Impactful Individual rankImpactful 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
        Icon for Solution Sage rankSolution 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.