Average price per product based on slicer

[mbbzoh]

Hi, 

I have table with columns

ProductID	Shop	Price
1	aaa	10
1	bbb	5
1	ccc	8
2	aaa	20
2	bbb	15
2	ccc	17
2	eee	14

I want to calculate a measure which show me average price per product based on slicer with shop.

It could be something like below:

1) For slicer shops in aaa & bbb

ProductID	AvgPrice
1	7,5
2	17,5

I've tried everithing what i know so please help someone 🙏

[v-venuppu]

Hi @mbbzoh ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[mbbzoh]

Thank you  ,but maybe i didn't explain it well. I need this average price to calculate ratio shop's price vs average price for all product separetly.

So in first step i need to have Averave product price like below: 

Shop	Product	Shop price	Average product price	Ratio (shop price/average price)	Volume my shop	Weight
1	aaa	10,00	15,00	0,67	10	6,67
2	aaa	20,00	15,00	1,33	10	13,33
1	bbb	5,00	10,00	0,50	15	7,50
2	bbb	15,00	10,00	1,50	15	22,50
1	ccc	8,00	12,50	0,64	5	3,20
2	ccc	17,00	12,50	1,36	5	6,80
2	eee	14,00	14,00	1,00	7	7,00

In second step i want to calculate weighted average for all shops based on ratio and volume my shop and get something like that in visual table::

Shop	Sum Weight	Sum Volume	Ratio (weight/volume)
1	17,36667	30	58%
2	49,63333	37	134%

[GeorgeColl · Accepted solution]

Hi @mbbzoh ,

For your first table make sure you use the table visual:

Then insert your fields as follows:

Where the following measures are defined as follows:

Sum of Shop Price = CALCULATE(
    SUM('Revised Data'[Shop price]))

AveragePrice = CALCULATE(
    AVERAGE('Revised Data'[Shop price]),
    FILTER(ALL('Revised Data'), 
        'Revised Data'[Product] = SELECTEDVALUE('Revised Data'[Product])
        ))

Sum of Volume = CALCULATE(
    SUM('Revised Data'[Volume my shop]))

Sum of Weight = CALCULATE(
    SUM('Revised Data'[Weight]))

This gives your first table.

For your second table, use the matrix visual

With your fields as follows:

Where VolumeRatio is a measure defined as:

VolumeRatio = CALCULATE(
    DIVIDE([Sum of Weight],[Sum of Volume], 0))

This will then give you the following final results: 

[mbbzoh]

Thank you @GeorgeColl ,but maybe i didn't explain it well. I need this average price to calculate ratio shop's price vs average price for all product separetly.

So in first step i need to have Averave product price like below: 

Shop	Product	Shop price	Average product price	Ratio (shop price/average price)	Volume my shop	Weight
1	aaa	10,00	15,00	0,67	10	6,67
2	aaa	20,00	15,00	1,33	10	13,33
1	bbb	5,00	10,00	0,50	15	7,50
2	bbb	15,00	10,00	1,50	15	22,50
1	ccc	8,00	12,50	0,64	5	3,20
2	ccc	17,00	12,50	1,36	5	6,80
2	eee	14,00	14,00	1,00	7	7,00

In second step i want to calculate weighted average for all shops based on ratio and volume my shop and get something like that in visual table::

Shop	Sum Weight	Sum Volume	Ratio (weight/volume)
1	17,36667	30	58%
2	49,63333	37	134%

[GeorgeColl]

Hi @mbbzoh, 

You can use the measure below to calculate the average price, if you then add a slicer for "Shop" you will get the result requested. 

AvgPrice = CALCULATE(
    AVERAGE('Table'[Price]))