Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

Grouped norm dist

Hi all,

 

I'm trying to make a grouped norm.dist calculation but can't yet figure it out. Below an example of my dataset (item, price):

 

chair30
couch400
table200
table150
chair11
desk100
chair60
couch180
chair100

 

If I would do a normal norm.dist calculation it wouldn't do any good since there are different products. That's why I want to group the calculation based on the product column. Each item should be calculated on it's own median and mean prices, and not on all the prices. It's btw big data with around 1K different items.

 

Any idea? Thanks in advance!

1 ACCEPTED SOLUTION

Aha, @Anonymous  try to add three columns like:

mean2 = 
VAR _table =
FILTER(
    TableName, 
    TableName[item] = EARLIER(TableName[item])
)
RETURN
AVERAGEX(
    _table,
    TableName[Price]
)

med2 = 
VAR _table =
FILTER(
    TableName,
    TableName[item] = EARLIER(TableName[item])
)
RETURN
CONVERT(
    MEDIANX(
        _table,
        TableName[Price]
    ), 
    INTEGER
)

std.dev2 = 
VAR _table =
FILTER(
    TableName,
    TableName[item] = EARLIER(TableName[item])
)
RETURN
STDEVX.P(
    _table,
    TableName[Price]
)

 

it worked like:

FreemanZ_0-1675436108002.png

View solution in original post

6 REPLIES 6
FreemanZ
Super User
Super User

hi @Anonymous 

try to plot a table visual with the product column and measures like:

 
mean = MEAN(TableName[Price])
median = MEDIAN(TableName[Price])
 
Or?
Anonymous
Not applicable

I would like to have the data in a calculated column, so that won't work. Besides that I dont want the distribution over all the prices but the distribution for each product within it's own prices.

hi @Anonymous 

try to create a calculated table like:
 
Table =
ADDCOLUMNS(
    VALUES(TableName[Product]),
    "mean",
     CALCULATE(MEAN(TableName[Price])),
    "median",
    CALCULATE(MEDIAN(TableName[Price]))
)
Anonymous
Not applicable

The problem is that it calculates everything on all the data, while you want to know the mean/median/stddev based on the items prices. Example;

 

itempricestddev
chair30A
couch400B
table200C
table150C
chair11A
desk100D
chair60A
couch180B
chair100A
Anonymous
Not applicable

The problem is that it calculates it over all the data ... I want to group the values based on the items. Example:

 

item pricemedmeanstd.dev
chair304550.2533.62
couch400290290110
table20017517525
table15017517525
chair114550.2533.62
desk100nullnullnull
chair604550.2533.62
couch180290290110
chair1004550.2533.62

 

The endgoal is to filter out outliers automatically within the report.

Aha, @Anonymous  try to add three columns like:

mean2 = 
VAR _table =
FILTER(
    TableName, 
    TableName[item] = EARLIER(TableName[item])
)
RETURN
AVERAGEX(
    _table,
    TableName[Price]
)

med2 = 
VAR _table =
FILTER(
    TableName,
    TableName[item] = EARLIER(TableName[item])
)
RETURN
CONVERT(
    MEDIANX(
        _table,
        TableName[Price]
    ), 
    INTEGER
)

std.dev2 = 
VAR _table =
FILTER(
    TableName,
    TableName[item] = EARLIER(TableName[item])
)
RETURN
STDEVX.P(
    _table,
    TableName[Price]
)

 

it worked like:

FreemanZ_0-1675436108002.png

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Kudoed Authors