Monthly conditions for multi-month averages

[XuanHau]

Hello guys,

I have 4 tables for:

- Revenue and unit

- Purchase value
- Master data for price of product

- Yearmonth table

I want to create one detailed table on report with product, revenue, unit, purchase value and price of product

Generally the price = revenue / unit and there's an additional condition: if there is no revenue or units for a particular month, then the price should be null, and we will use the price from the master data instead for that month.

Unfortunately, I have a slicer that can select multiple months, so I need to calculate the average price after applying the above condition

For example:

Product B in the image: when choosing January, since we do not have both revenue and units, the price retrieved from the master data is 7. When selecting February, the price is calculated as 30/5 = 6. Therefore, when selecting January and February, the price of Product B would be 6.5.

So, Is there any way to apply a condition per month for the price first, and then calculate the average for multiple months?

Thank you in advance!

[v-yilong-msft]

Hi @XuanHau ,

I create three tables as you mentioned.

Then I create a measure and you will get what you need.

Average =
DIVIDE ( ( SUM ( Jan[Price] ) + SUM ( Feb[Price] ) ), 2 )

Best Regards

Yilong Zhou

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

[XuanHau]

Hi @v-yilong-msft 

The table for January, February, and the sum of January and February is a detailed table that I need to display on the report with data corresponding to the selected month/year slicer. It is not fixed fact table data, so I cannot use your suggestion

Thank you for your help!