Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

2nd Calculated Column Formula

I have 3 Raw Data Columns (Date, Group, Number) and 1 Calculated Column (80). I'm trying to make a 2nd Calculated Column where the denominator is the sum of the first calculated column for each row o...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    7 years ago

    HiAnonymous ,

     

    Try the following replace Table with your tablename

     

    Percents =
VAR _total80 =
    CALCULATE (
        SUM ( 'Table'[80] ),
        FILTER ( 'Table', [Date] = EARLIER ( 'Table'[Date] ) )
    )
RETURN
    DIVIDE ( [80], _total80 )

    Cheers

     

    CheenuSing

  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Anonymous 

     

     

    _total80 is a temproary variable created to compute the totals of column named 80 in your dataset, for each date.

         FILTER ( 'Table', [Date] = EARLIER ( 'Table'[Date] ) )  checks if the current date is same as earlier row  date and groups them together.

     

    Cheers

     

    CheenuSing

     

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

HiAnonymous ,

 

Try the following replace Table with your tablename

 

Percents =
VAR _total80 =
    CALCULATE (
        SUM ( 'Table'[80] ),
        FILTER ( 'Table', [Date] = EARLIER ( 'Table'[Date] ) )
    )
RETURN
    DIVIDE ( [80], _total80 )

Cheers

 

CheenuSing

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hello, 

    Can you break down what this is doing?

    It works for the test data I posted, but not for my real/master file. 

     

    VAR _total80 =
        CALCULATE (
            SUM ( 'Table'[80] ),
            FILTER ( 'Table', [Date] = EARLIER ( 'Table'[Date] ) )
        )
    RETURN
        DIVIDE ( [80], _total80 )

     

    What is _total80?

    If I filter by [Date] = EARLIER ('Table'[Date]) what is that doing?

     

    TYIA

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Anonymous 

       

       

      _total80 is a temproary variable created to compute the totals of column named 80 in your dataset, for each date.

           FILTER ( 'Table', [Date] = EARLIER ( 'Table'[Date] ) )  checks if the current date is same as earlier row  date and groups them together.

       

      Cheers

       

      CheenuSing

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Thank you! very much appreciate the additional explanation.