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!
