Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm wondering if it was possible to get an average of all peoples' Percent Changes (which was a custom measure). In other words, add all the percent changes in the table and divide by the sample size.
For the calculation I created a measure with this formula (below) and was able to show each individual person’s percent change using a Matrix, but I’m not sure how I can perform this calculation on those numbers (under Percent Change).
Percent Change = DIVIDE(
CALCULATE(SUM(WeightData[Weight]),WeightData[Date]=Max(WeightData[Date])) -
CALCULATE(SUM(WeightData[Weight]),WeightData[Date]=Min(WeightData[Date])),
CALCULATE(SUM(WeightData[Weight]),WeightData[Date]=Max(WeightData[Date])),0)
Solved! Go to Solution.
Hi @CarloA93 ,
Based on your description, I have created a simple sample:
Please try:
Average Percent Change =
VAR _a =
DISTINCT ( SELECTCOLUMNS ( 'WeightData', "PatientName", [Patient Name] ) )
VAR _b =
ADDCOLUMNS (
_a,
"PercentageChange",
(
DIVIDE (
CALCULATE (
SUM ( WeightData[Weight] ),
FILTER (
'WeightData',
[Patient Name] = EARLIER ( [PatientName] )
&& [Date]
= MAXX (
FILTER ( 'WeightData', [Patient Name] = EARLIER ( [PatientName] ) ),
[Date]
)
)
)
- CALCULATE (
SUM ( WeightData[Weight] ),
FILTER (
'WeightData',
[Patient Name] = EARLIER ( [PatientName] )
&& [Date]
= MINX (
FILTER ( 'WeightData', [Patient Name] = EARLIER ( [PatientName] ) ),
[Date]
)
)
),
CALCULATE (
SUM ( WeightData[Weight] ),
FILTER (
'WeightData',
[Patient Name] = EARLIER ( [PatientName] )
&& [Date]
= MAXX (
FILTER ( 'WeightData', [Patient Name] = EARLIER ( [PatientName] ) ),
[Date]
)
)
),
0
)
)
)
RETURN
AVERAGEX ( _b, [PercentageChange] )
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @CarloA93 ,
Based on your description, I have created a simple sample:
Please try:
Average Percent Change =
VAR _a =
DISTINCT ( SELECTCOLUMNS ( 'WeightData', "PatientName", [Patient Name] ) )
VAR _b =
ADDCOLUMNS (
_a,
"PercentageChange",
(
DIVIDE (
CALCULATE (
SUM ( WeightData[Weight] ),
FILTER (
'WeightData',
[Patient Name] = EARLIER ( [PatientName] )
&& [Date]
= MAXX (
FILTER ( 'WeightData', [Patient Name] = EARLIER ( [PatientName] ) ),
[Date]
)
)
)
- CALCULATE (
SUM ( WeightData[Weight] ),
FILTER (
'WeightData',
[Patient Name] = EARLIER ( [PatientName] )
&& [Date]
= MINX (
FILTER ( 'WeightData', [Patient Name] = EARLIER ( [PatientName] ) ),
[Date]
)
)
),
CALCULATE (
SUM ( WeightData[Weight] ),
FILTER (
'WeightData',
[Patient Name] = EARLIER ( [PatientName] )
&& [Date]
= MAXX (
FILTER ( 'WeightData', [Patient Name] = EARLIER ( [PatientName] ) ),
[Date]
)
)
),
0
)
)
)
RETURN
AVERAGEX ( _b, [PercentageChange] )
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you. I think this will work, but I will let you know if I have any questions.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |