Calculating Row Value against Total of the same Column

[NotSoClever]

Hello, 

I've been trying to generate the following formula to get an impact from a giving set of values (To Achieve field). Tried different DAX functions and not quite close to get the sum of all rows from the same column. 

basically, the formula is as follow:

=((TotalBase - RowBase + (RowSample * Goal)) / TotalSample) - Average

i.e first row= ((16611.91 - 200 + (2 * 85)) / 199) - 83.4769

I would like to display the measure result on a table after placing the first three columns.

Samples are individual rows with their original score/result.

	83.4769	199	16611.91	85
Name	Average	Sample	BASE	GOAL	To achieve
A	100.0	2	200	85	-15.08%
B	100.0	2	200	85	-15.08%
C	100.0	2	200	85	-15.08%
D	100.0	2	200	85	-15.08%
E	98.2	33	3241.92	85	-219.56%
F	97.9	14	1370.04	85	-90.47%
G	97.7	15	1465.05	85	-95.50%
H	93.1	18	1675.98	85	-73.36%
I	92.9	18	1672.02	85	-71.37%
J	85.7	7	599.97	85	-2.50%
K	85.1	7	595.98	85	-0.49%
L	84.9	17	1443.98	85	0.51%
M	75.3	23	1730.98	85	112.57%
N	75.2	9	676.98	85	44.23%
O	75.0	4	300	85	20.10%
P	44.0	1	44	85	20.60%
Q	44.0	1	44	85	20.60%
R	41.4	5	207	85	109.55%
S	41.0	14	574	85	309.55%
T	34.7	3	104.01	85	75.87%
U	33.0	2	66	85	52.26%

Appreciated any guidance you can provide. 😃

[lbendlin]

You may want to validate your expected result. It doesn't match your formula.

Note that "Goal"  and "Average"  are reserved words.

[NotSoClever]

Hi Ibendlin,

Thank you very much for your time. This is somehow a similar approach i tried, but unfortunately it is not showing a correct/desired result. I think I had to specify how the table is structured. 

Basically the above is dummie data. Lets say instead of a summarized table, my original Table data contains 199 rows (samples) each one with their individual Average/result [just three columns (Name/Average/Sample)]. My Overall average (Average to use in the calculation) would be the average of my Average column, and thats the result I need to consider.

A bit of what I'm trying to do: its kind of a Cross-Multiplication (considering the individual average against the Overall Average) which will tell me if a NAME reaches GOAL how much will my Overall Average (AVERAGE) will increase (at least for those below goal). I.e. the last row U, if somehow this item achieve 85 goal, my Overall will increase by 0.52 (now 84).

On the above approach its showing -15 where actually it would be 1.53 (which is the difference from Goal).

Again, I really really appreciate your time on helping out. Would you have another idea/approach on how this can be solved?

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...