Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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..
Solved! Go to 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
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
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
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
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
Thank you! very much appreciate the additional explanation.
User | Count |
---|---|
96 | |
86 | |
78 | |
72 | |
67 |
User | Count |
---|---|
110 | |
104 | |
84 | |
65 | |
63 |