Normal Distribution on Aggregated Values using Average for Last 5 Years

[RB_B_ABI_01]

Hi All,

I am trying to replicate an Exel formula based on Normal Distribution as a Measure in Power BI. In Excel this formula is used on aggregated values of Year and Region.

In Power BI I want the formula to work on the unaggregated dataset to avoid having to create and work off a new table.

The formula is as follows:

NORMDIST(X, avg(last 5 yrs), SD (last 5 yrs) * 1.5) * 100

Where X is the value I want to see where it sits on the distribution.

The data (simplified) is as follows:

Record No.	Category	Allocation	Approval	Year	Region
1	A	1	Yes	2020	South
1	B	1	Yes	2020	South
2	C	1	No	2020	North
2	D	1	No	2020	North
3	A	1	No	2022	West
3	D	1	No	2022	West
3	E	1	No	2022	West

Records can be assigned multiple categories and these have been pivoted to show one category per row. Because of this the Allocation column is always 1.

I would like the equation to be a single measure so I can easily switch between this and the sum of allocation/approval measures I already have.

I would want the output to look something like this:

Year	A	B	C	D	E
2020 >	43	56	48	71	28
South	28	56	45	24	58
North	68	15	47	49	57
West	57	58	25	47	85
2021	62	50	48	69	42
2022	56	47	32	58	45

Thanks in advance!

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.