Calculate the category average of the selected item

[Anonymous]

Hello,

I need to calculate a measure for make a comparison between an value selected and the average value of te category his belongs.

E-R:

In the fact table I have my data. Depending on the Symbols that I select in the "Tickets" table, I get a value x. What I want to know is how to calculate the average value of the Industry to which this ticket belongs (Table "Industry").

Thanks!

[v-henryk-mstf]

Hi @Anonymous ,

According to your description conditions, you can add the following conditions, select the specified symbol and calculate its corresponding avg.

M =
VAR sel =
    SELECTEDVALUE ( 'Table'[symbol] )
RETURN
    IF (
        MAX ( 'Table'[symbol] ) = sel,
        AVERAGEX ( VALUES ( Ticket[Ticket ID] ), CALCULATE ( SUM ( 'Table'[Value] ) ) ),
        BLANK ()
    )

If there is still confusion, can you provide some test data and screenshots of the expected results, and I will answer you as soon as possible.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

I show you an example of what I want based on some supposed data:

The average value of the sector is calculated based on the average of the values of each company that belongs to the health sector.

Sector	Average value
Bless you	10
Banks	5
Constructor	20

Company	Value	Sector
A	15	Bless you
B	5	Sañud
C	5	Bench
D	20	Constructor

This is my dashboard:

I want to calculate a metric that is dynamically modified depending on the company you choose. For example, it belongs to the HEALTH sector. Therefore, I want my metric to return the average value for health. However, if I choose another company I want the metric to detect which sector it belongs to and then calculate the average of the sector.

[amitchandak]

@Anonymous 

do what to sum first and do avg ? like some tll ticket number then avg ?

AverageX(Values(Ticket[Ticket ID]) , Calculate(Sum(Fact[Value]) ) )

[Anonymous]

I want to average the value for the industry. The difficulty I find is that I need the industry to be selected based on the symbol that I select