Hello! Im stuck in something that seems easy but i havent got too much experience with DAX hence i cant move forward:
So i have a table like this:
| Names | Category 1 | Category 2 | Category 3 | Total |
| Name 1 | 44% | 32% | 24% | 100% |
| Name 2 | 23% | 45% | 32% | 100% |
| Name 3 | 33% | 63% | 4% | 100% |
| Name 4 | 34% | 23% | 43% | 100% |
Where the inside values are the percentage of totals calculated like this:
Percentage of total =
DIVIDE(
SUM('Base'[Value]),
CALCULATE(
SUM('Base'[Value]),
ALL('Base'[Category])
),
0
)
What i indend to get is the Mean and Standard Deviation to get something like this:
| | Mean | Stdev |
| Category 1 | 34% | 7.43% |
| Category 2 | 41% | 15.04% |
| Category 3 | 26% | 14.25% |
I calculated them this way:
Category Mean Percentage =
AVERAGEX(
ALL('Base'[Names]),
[Percentage of total]
)
Stdev Percentage Category=
STDEVX.P(
ALL('Base'[Names]),
[Percentage of total]
)
and i actually get the right values but my problem is that later i want to color cells based on the stdev and mean so i did this:
Cell Color Half StdDev =
VAR CurrentValue = [Percentage of total]
VAR MeanValue = [Category Mean Percentage]
VAR StdDevValue = [Stdev Percentage Category]
VAR HalfStdDevBelow = MeanValue - (0.5 * StdDevValue)
VAR HalfStdDevAbove = MeanValue + (0.5 * StdDevValue)
VAR IsOutsideHalfStdDev = CurrentValue < HalfStdDevBelow || CurrentValue > HalfStdDevAbove
RETURN
IF(
ISBLANK(CurrentValue), BLANK(), // Return blank if there's no sales
IF(
IsOutsideHalfStdDev, "#FDFD96", // Pastel Yellow for outside half a standard deviation
"No Color" // No color if inside half a standard deviation
)
)
I checked and think the problem is how the Mean and Stdev are being calculated cause all my cells get highlighted and its because it calculates as well the mean and stddev per each Name and Category so the Mean and stdev for each name is the same as its %of total.
So in theory the mean should look like this for each Name:
| | Mean |
| Name | Category 1 | Category 2 | Category 3 |
| Name 1 | 34% | 41% | 26% |
| Name 2 | 34% | 41% | 26% |
| Name 3 | 34% | 41% | 26% |
| Name 4 | 34% | 41% | 26% |
but it looks like this:
| Name | Category 1 | Category 2 | Category 3 |
| Name 1 | 44% | 32% | 24% |
| Name 2 | 23% | 45% | 32% |
| Name 3 | 33% | 63% | 4% |
| Name 4 | 34% | 23% | 43% |
Note: I have many slicers on the page that filter the data like year month brand etc.
Im sorry if im terrible at explaining!
