Average Quantities and Prices: how to use and ignore filter simultaneously

[Anonymous]

Hi all,

Sample PBIX: Average Quantities & Prices 

I have a model including Sales, Products, Prices and Months:

Per product, I need to calculate an average quantity sold per month across all selected months and then multiply that average with different prices (depending on the month). In the end, I want to include the Month (and Product ofcourse) in my visuals.

The measure for the average looks like this:

Average = 
CALCULATE(
DIVIDE(SUM(Sales[Quantity]),DISTINCTCOUNT(Months[Month])), 
Sales[Category] in {"A","B"}, 
ALLSELECTED(Months[Month])
)
* DISTINCTCOUNT(Months[Month])

The table Months contains distinct Year-Month combinations which is connected to the Sales table.

I end up with a table like this:

Product Name	Month	Average
Beer	2024-01	100
Beer	2024-02	100
Beer	2024-03	100
Beer	2024-04	100
Beer	2024-05	100
Total		500

This table includes Product Name which is from a separate table connected to the Sales table. The visuals I'm creating are filtered in the report by this Product Name and Month. For simplicity, I'm filtering on 'Beer' only. Apparently, in the first 5 months of 2024 we sold 100 beers on average per month.

So far, so good.

Now it gets interesting: I have a separate table with prices that is one-to-one connected with my Products table. It contains a Product, and two columns. The first column is the price for 2024Q1 (so months 2024-01, 2024-02, 2024-3) and the second column  is the price for 2024Q2 (so months 2024-04, 2024-05, 2024-6).

Let's say the price in 2024Q1 was $4 and in 2024Q2 it was $5.

I need to create two visuals/tables..

1)

Product Name	Month	Average	Sales
Beer	2024-01	100	$400
Beer	2024-02	100	$400
Beer	2024-03	100	$400
Beer	2024-04	100	$500
Beer	2024-05	100	$500
Total		500	$2.200

--> This visual is filtered by a single Product Name and Month.

2)

Product Name	Sales
Beer	$2.200
Soda	$2.040
Water	$972
Total	$5.212

--> This visual is filtered by Month. Notice the multiple Product Names.

I'm really struggling to create a working measure for 'Sales'.

This is what I came up with myself but I don't know how to account for the months that must make use of 'PriceQ2':

Sales = 

SUMX(Products, RELATED(Prices[PriceQ1]) * [Average])

These are the results with this measure:

This works fine if there was only one price to multiply with. But I have a second (higher) price for the months 2024-04 and 2024-05! How do I create the second part of the Sales measure that accounts for PriceQ2?

Your help/advice is very much needed and appreciated. 🙂 

Thanks for reading! Let me know if there's something in need of clarification.
 

[Anonymous · Accepted solution]

Hi, @Anonymous 

You can try the following methods. Add a calculated column.

Prices = SWITCH(TRUE(),
QUARTER(Sales[Date])=1,CALCULATE(MAX(Prices[PriceQ1]),FILTER(Prices,[Product Name]=EARLIER(Sales[Product Name]))),
QUARTER(Sales[Date])=2,CALCULATE(MAX(Prices[PriceQ2]),FILTER(Prices,[Product Name]=EARLIER(Sales[Product Name]))))

Measure = MAX(Sales[Prices])*[Average]

Result = 
Var _table=SUMMARIZE(Sales,Sales[Product Name],Sales[Month],"Sum",[Measure])
RETURN
IF(HASONEVALUE(Sales[Month]),[Measure],SUMX(_table,[Sum]))

Is this the result you expected? Please refer to the second page of the attached.

Best Regards,

Community Support Team _Charlotte

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

[Anonymous]

Hi, thanks for your answer! I was hoping for a more elegant solution but it seems to be working like this, also in my actual model. Thanks again!