2nd Calculated Column Formula

[kressb]

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 of the same date:

Date	Group	Number	80
5/9/2019	Blue	0.5	0.00625	40.00%
5/9/2019	Green	0.25	0.003125	20.00%
5/9/2019	Blue	0.5	0.00625	40.00%
5/10/2019	Red	0.15	0.001875	15.79%
5/10/2019	Red	0.3	0.00375	31.58%
5/10/2019	Yellow	0.5	0.00625	52.63%

So the denominator for the first three rows (5/9/19) would be 0.015625

     the denominator for the second three rows (5/10/19) would be 0.011875

The 2nd calculated column would add to 100% for all the rows of the same date. 

Can anyone advise on the correct formula to do this in PowerBI?

*ETA: right now I am using: Divide(Table[80],sum(Table[80]),0) but not sure if I'm missing something..

[CheenuSing · Accepted solution]

Hi@kressb ,

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

[CheenuSing · Accepted solution]

Hi@kressb ,

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

[kressb]

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

[CheenuSing · Accepted solution]

Hi @kressb 

_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

[kressb]

Thank you! very much appreciate the additional explanation.