Averages, Filters & Totals with changing row-context

[Anonymous]

UPDATE: Due to some errors in my question, I opened a new Topic. See: Average Quantities and Prices: how to use and igno... - Microsoft Fabric Community

Sample PBIX: Averages, Filters & Totals Sample Data 

Hi,

I have a measure that looks like:

Average = 
CALCULATE(
DIVIDE(SUM(Sales[Quantity]),DISTINCTCOUNT(Months[Month])), 
Sales[Category] in {"A","B"}, 
ALLSELECTED(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		100

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.

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. Notice the '500' in the total row as opposed the the first table I showed which gives '100' in the total row.

2)

Product Name	Sales
Beer	$2.200
Soda	$4.100
Water	$3.700
Total	$10.000

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

I'm really struggling to create a working measure for 'Sales'. Already in the Average measure, I can't get PowerBI to sum the individual averages per month in the bottom row with totals. It always shows the '100', also if I leave out the months.

This is what I came up with myself but it's giving me weird/incorrect numbers:

Sales = 

VAR SalesQ1 =
CALCULATE(SUMX(FILTER(Sales, RELATED(Months[Month]) in {"2024-01","2024-02","2024-03"}), [Average]*RELATED(Prices[PriceQ1])), ALLSELECTED(Months[Month]))

VAR SalesQ2 =
CALCULATE(SUMX(FILTER(Sales, RELATED(Months[Month]) in {"2024-04","2024-05","2024-06"}), [Average]*RELATED(Prices[PriceQ2])), ALLSELECTED(Months[Month]))

RETURN
SalesQ1 + SalesQ2

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

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

[Anonymous]

Hi @Anonymous 

Thanks for the reply from bhanu_gautam .

It looks like you used three tables. Since I don't know your data structure, I created a simple sample data to implement your first requirement for your reference. If my formula does not work for your scenario, please provide some sample data so that we can better help you. How to provide sample data in the Power BI Forum - Microsoft Fabric Community. Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

Regarding your second requirement, I'm afraid we need you to provide sample data of the tables used to better help you.

Sample:

Create a measure as follows

Average = 
IF (
    ISFILTERED ( 'Sales'[Month] ),
    CALCULATE (
        AVERAGE ( Sales[Quantity] ),
        ALLEXCEPT ( Sales, Sales[Product Name] )
    ),
    SUM ( 'Sales'[Quantity] )
)

Output:

Best Regards,
Yulia Xu

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 reply. I included a sample PBIX file in my original post. Could you please help me? I tried your measure for 'Average', but it's not giving me the correct result.

[bhanu_gautam]

@Anonymous ,First, you need to create a measure for Sales that takes into account the different prices for each quarter

Sales =
VAR SalesQ1 =
CALCULATE(
SUMX(
FILTER(
Sales,
Sales[Month] IN {"2024-01", "2024-02", "2024-03"}
),
[Average] * RELATED(Prices[PriceQ1])
),
ALLSELECTED(Months[Month])
)
VAR SalesQ2 =
CALCULATE(
SUMX(
FILTER(
Sales,
Sales[Month] IN {"2024-04", "2024-05", "2024-06"}
),
[Average] * RELATED(Prices[PriceQ2])
),
ALLSELECTED(Months[Month])
)
RETURN
SalesQ1 + SalesQ2

Ensure your Average measure is correctly defined to calculate the average quantity per month:

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

[Anonymous]

Same incorrect result as from my own measures. What did you change?