Average Calculation

[tobisw]

Hello everybody,

i´m struggeling with a average calculation in my power bi report. Target what i would like to achieve is, to calculate the 

• Range of Coverage as average of all parts of a MRP controller in a plant

My Table "PLANTS" structure looks following:

Plant Code	Plant Material	MRP Controller	Attribut	Amount
8401	2402000508	600	Req./Day	34,1
8401	2402000508	600	Actual Range	16,6
8401	2402000508	600	Stock	200
7601	494600125	500	Req./Day	10
7601	4946000125	500	Actual Range	3
7601	4946000125	500	Stock	3000

The calculation what i did was following:

1. I created a SUM Measure with following Code:

  SUM Plants =  

  SUM(

  Plants[Amounts])

2. Created Measure of Actual Range:

     Actual_range =

     CALCULATE(

     [SUM Plants],

     Plants[Attribut]="Actual range")

2. Created Measure of Average Demand per Day :

    Average_demand_day =

    CALCULATE(

    [SUM Plants],

    Plants[Attribut]="Req./Day")

3. By using Power BI Quickmeasure Creation i wanted to have the Average of "Range of Coverage" of all parts of a MRP Controller in a plant.

Average_Actual_range pro MRP Contr. =

AVERAGEX(

    KEEPFILTERS(VALUES('Plants'[MRP Contr.])),

    CALCULATE([Actual_range])

)

In my final report, the "Average_Actual_range pro MRP Contr." is sum up everything. My target would be, that the average is not summing up and will show the average result of each single MRP controller.

Thanks

Tobias

[amitchandak]

@tobisw , This formula, will avg above 'Plants'[MRP Contr.] level

AVERAGEX(
KEEPFILTERS(VALUES('Plants'[MRP Contr.])),
CALCULATE([Actual_range])
)

or

AVERAGEX(
VALUES('Plants'[MRP Contr.]),
CALCULATE([Actual_range])
)

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[tobisw]

You mean, that my formula is not correct? Sorry, i´m not sure if i understood your right. By using your formula the result is the same...

[Anonymous]

Hi @tobisw ,

I have a test by your code. And get result as below.

I think the average of "Acutal_range" in each [MPR Controller] is correct.

For [MPR Controller] = 500, average of "Acutal_range"  = 3/1

For [MPR Controller] = 600, average of "Acutal_range"  = 166/1

Subtotal = (3+166)/2

Could you show a screenshot with the result you want to me?

Best Regards,
Rico Zhou

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