Get the Average of a measure

[Anonymous]

I have data in power bi in this example format:

SKU	ecap price	asp per core	core count
A1	200	31	5
B2	227	32.4	2
C4	499	57.9	6

"Asp per core" is a caluclated column which uses the formula :

ASP per Core = (Table5[Average SKU ECAP ($)] / Table5[Core Count])

and "Average SKU ECAP ($)" is a measure which uses the formula :

Average SKU ECAP ($) = CALCULATE(

AVERAGE(Table5[Ecap Price ($)]),

ALLEXCEPT(Table5,Table5[Processor Number])

)

I then made a matrix visualisation like below:

I want to make another table visulation like above, but it shows me the core count and the average of average asp per core e.g.:

Taking below porcessors with 10 cores data 

Core Count	Average of Average ASP per core
10	60.34

And rest table would show average of average asp per core, for all 1 core, 2 core, 3 core processors etc.

[Anonymous]

Hi @Anonymous ,

I have some doubt.

•  The Processor number are quite different,Why the last three use averages? How can you tell if they are a group? Is the first core count number the same? 

  If so, please have a try.

measure=averagex(filter(all(table),table[first core count]=selectedvalue(table[first core count]),[average asp per core])

• If not, please provide more details.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

[Anonymous]

Sorry, iam using last 3 as an example as they are the only three processors with 10 cores, however this table i showed, is showing a summary of the data in a amtrix table, so there will be lots of rows of these 3 skus. I use first core count, as for some reason it doesnt allow just core count, i think because its a summary in the matrix, but just they all have 10 cores, same for e.g. if you take D-2753NT example (the row above) every row in my dataset which uses thatprocessor always has 12 cores etc

[Anonymous]

Hi @Anonymous ,

If so, please have a try.

measure =
AVERAGEX (
    FILTER (
        ALL ( table ),
        table[first core count] = SELECTEDVALUE ( table[first core count] )
    ),
    [average asp per core]
)

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

[Anonymous]

Hey,

First Core Count isnt actualy a column, its just when i use my Core Count column, it in the matrix table it maks me choose one of the below 

[Anonymous]

Hi @Anonymous ,

Is it a measure? The conditions offered are different each time, and I can't understand what you mean correctly.

Could you please provide more details with your desired output and pbix file without privacy information (or some sample data)  for me to test?

Best Regards
Community Support Team _ Polly

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

[Anonymous]

SKU	ECAP	core count
A	5	2
B	8	3
A	10	2
C	2	4
C	3	4
D	6	2
D	8	2

Above is an example of the raw dataset i feed into powerbi,

the ecap price can be differnet even if it is the same processor (but not by much), the core count however will always remain the same for the same processor, but 2 differnt processors can have the same core count.

I need to achieve 2 things:

1. Create a matrix table visulation that displays all the distinct processors, and then shows the average ecap of each processor divided by its core count, e.g. in above example average of processor A would be average of 5 and 10 so 7.5, then divided by its core count of 2.

2. I want another matrix table that shows all the differnt distinct core counts, e.g. in above ddataset example 2,3 and 4, as row values and then next to it shows the average of the average ecap core count (which is the value we calculated in (1) the above requested).

E.g. in the above table dataset for core count 2 the matrix would look like so

Core count | Average of average ecap core count

        2         |     3.625

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please refer to my pbix file to see if it helps you.

Measure2 =
VAR _1 =
    AVERAGEX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[core count] = SELECTEDVALUE ( 'Table'[core count] )
        ),
        'Table'[ECAP]
    ) //var _2=CALCULATE(DISTINCTCOUNT('Table'[SKU]),FILTER(ALL('Table'),'Table'[core count]=SELECTEDVALUE('Table'[core count])))
RETURN
    _1 / MAX ( 'Table'[core count] )

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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