Get Mean and Standard Deviation per Category

Anonymous — Fri, 15 Mar 2024 11:32:22 GMT

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!

Re: Get Mean and Standard Deviation per Category

lbendlin — Fri, 15 Mar 2024 21:13:07 GMT

correct. Read about REMOVEFILTERS - that will help you to get the right number in the most gentle way.

Re: Get Mean and Standard Deviation per Category

Anonymous — Mon, 18 Mar 2024 10:25:32 GMT

Thanks! ill read about it

Re: Get Mean and Standard Deviation per Category

talespin — Mon, 18 Mar 2024 12:35:32 GMT

hi Anonymous

If possible, please share pbix with mock data.

topic Re: Get Mean and Standard Deviation per Category in DAX Commands and Tips

Get Mean and Standard Deviation per Category

Re: Get Mean and Standard Deviation per Category

Re: Get Mean and Standard Deviation per Category

Re: Get Mean and Standard Deviation per Category