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

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.