cancel
Showing results for
Did you mean: Frequent Visitor

## Aggregating / Summarizing the result of calculated measures using weighted average method

Hi,

I want to calculate the (money) weighted average index for product price changes over selected time period. The weighted price changes needs to be calculated not only on product level, but also on category level and for all categories.

I have a sample data in the following table.

 Category Product sales date Price QTY Sales TOOLS Product A 1.1.2021 10 1 10 TOOLS Product A 1.8.2021 12 2 24 TOOLS Product B 1.3.2021 13 1 13 TOOLS Product B 1.10.2021 15 1 15 APPLIANCES Product C 1.1.2021 18 2 36 APPLIANCES Product C 1.4.2021 15 3 45 APPLIANCES Product D 10.1.2021 9 4 36 APPLIANCES Product D 12.2.2021 5 2 10

I manage to calculate the price change on product level based on difference between first date and last date sales price.

 Category Product First sales price Last sales price Price change over time Sales % of category sales TOOLS Product A 10 12 20 % 34 55 % TOOLS Product B 13 15 15 % 28 45 % APPLIANCES Product C 18 15 -17 % 81 64 % APPLIANCES Product D 9 5 -44 % 46 36 %

I used the following formula.

"

Price change over time =
DIVIDE(
CALCULATE (
MIN('Fact table'[Price]),
LastDATE ('Fact table'[sales date])
),
CALCULATE(
MIN('Fact table'[Price]),
FIRSTDATE( ('Fact table'[sales date])))
)-1

For the sales weighted percentages I have used the following formula.,

"

Percentage (%) of total =
VAR CurrentSales = SUM('Fact table'[Sales])
VAR AllSelectedSales = SUMX(ALL('Fact table'),'Fact table'[Sales])
RETURN
DIVIDE(CurrentSales,AllSelectedSales)

The challenge for me is how to calculate (summarized/aggregated) price change index for category level/All using sales weighted measure and also keeping the possibility to filter the desired result with time selection. Thus, there is a need to summarize/aggregate calculate measure, when it is hard to apply e.g. SUMX function. Creating separate table is difficult, because there must be possibility to filter end result based on the time selection.

So the need is to calculate the "Sales weighted Price change" in Power BI, so that result will be following (I have calculated the results in excel)

 Category Sales Weighted Price Change Sales % of total TOOLS 18 % 62 33 % APPLIANCES -27 % 127 67 % 189 Sales Weighted Price Change TOTAL -12 % 189

I will appreciate your help and knowledge to solve the challenge.

1 ACCEPTED SOLUTION  Community Support

Hi @ikavaju ,

According to your description, you can modify the formula like this:

``````Price change over time 2 =
VAR _D =
DIVIDE (
MAXX (
FILTER (
ALL ( 'Fact table' ),
'Fact table'[sales date]
= MAXX (
FILTER (
ALL ( 'Fact table' ),
'Fact table'[Product] = EARLIER ( 'Fact table'[Product] )
),
'Fact table'[sales date]
)
&& 'Fact table'[Product] = EARLIER ( 'Fact table'[Product] )
),
'Fact table'[Price]
),
MAXX (
FILTER (
ALL ( 'Fact table' ),
'Fact table'[sales date]
= MINX (
FILTER (
ALL ( 'Fact table' ),
'Fact table'[Product] = EARLIER ( 'Fact table'[Product] )
),
'Fact table'[sales date]
)
&& 'Fact table'[Product] = EARLIER ( 'Fact table'[Product] )
),
'Fact table'[Price]
)
) - 1
RETURN
IF (
_D > 1
|| _D < -0.9,
BLANK (),
_D
/ COUNTROWS (
FILTER (
ALL ( 'Fact table' ),
'Fact table'[Product] = EARLIER ( 'Fact table'[Product] )
)
)
)
``````

I modify my sample data and it can get correct result. I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

5 REPLIES 5  Community Support

Hi @ikavaju ,

According to your description, I create a sample and get the expected result by calculated columns, here's my solution.

1.Create three calculated columns.

``````Price change over time 2 =
(
DIVIDE (
MAXX (
FILTER (
ALL ( 'Fact table' ),
'Fact table'[sales date]
= MAXX (
FILTER (
ALL ( 'Fact table' ),
'Fact table'[Product] = EARLIER ( 'Fact table'[Product] )
),
'Fact table'[sales date]
)
&& 'Fact table'[Product] = EARLIER ( 'Fact table'[Product] )
),
'Fact table'[Price]
),
MAXX (
FILTER (
ALL ( 'Fact table' ),
'Fact table'[sales date]
= MINX (
FILTER (
ALL ( 'Fact table' ),
'Fact table'[Product] = EARLIER ( 'Fact table'[Product] )
),
'Fact table'[sales date]
)
&& 'Fact table'[Product] = EARLIER ( 'Fact table'[Product] )
),
'Fact table'[Price]
)
) - 1
)
/ COUNTROWS (
FILTER (
ALL ( 'Fact table' ),
'Fact table'[Product] = EARLIER ( 'Fact table'[Product] )
)
)
``````
``````Percentage (%) of total2 =
VAR CurrentSales =
SUMX (
FILTER (
ALL ( 'Fact table' ),
'Fact table'[Product] = EARLIER ( 'Fact table'[Product] )
),
'Fact table'[Sales]
)
VAR AllSelectedSales =
SUMX (
FILTER (
ALL ( 'Fact table' ),
'Fact table'[Category] = EARLIER ( 'Fact table'[Category] )
),
'Fact table'[Sales]
)
RETURN
( DIVIDE ( CurrentSales, AllSelectedSales ) )
/ COUNTROWS (
FILTER (
ALL ( 'Fact table' ),
'Fact table'[Product] = EARLIER ( 'Fact table'[Product] )
)
)
``````
``````Sales weighted Price change2 =
SUMX (
FILTER (
ALL ( 'Fact table' ),
'Fact table'[Product] = EARLIER ( 'Fact table'[Product] )
),
'Fact table'[Percentage (%) of total2] * 'Fact table'[Price change over time 2]
)
``````

2.Create a measure.

``% of total = SUM('Fact table'[Sales])/SUMX(ALL('Fact table'),'Fact table'[Sales])``

Get the expected result. I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

Thanks for the innovative solution proposal. It contains some new DAX code for me. I need to verify the solution with the wider data scope. Let me some time to check this, then I will come back and ask possibly further questions or accept the solution. I really appreciate your help 🙂 Frequent Visitor

It, seems, that "Price change over time 2" column works well based on my test.

However, in huge data set, there are some unrealistic price changes due to inaccurate source data. Thus. is there possiblity to set filters to calculated column price changes (actually replace them with 0 %) which are under -90% and over 100% for example.  So, this as a additional question. So, I would like to know how to modify the calculated  column "Price change over time"  Community Support

Hi @ikavaju ,

According to your description, you can modify the formula like this:

``````Price change over time 2 =
VAR _D =
DIVIDE (
MAXX (
FILTER (
ALL ( 'Fact table' ),
'Fact table'[sales date]
= MAXX (
FILTER (
ALL ( 'Fact table' ),
'Fact table'[Product] = EARLIER ( 'Fact table'[Product] )
),
'Fact table'[sales date]
)
&& 'Fact table'[Product] = EARLIER ( 'Fact table'[Product] )
),
'Fact table'[Price]
),
MAXX (
FILTER (
ALL ( 'Fact table' ),
'Fact table'[sales date]
= MINX (
FILTER (
ALL ( 'Fact table' ),
'Fact table'[Product] = EARLIER ( 'Fact table'[Product] )
),
'Fact table'[sales date]
)
&& 'Fact table'[Product] = EARLIER ( 'Fact table'[Product] )
),
'Fact table'[Price]
)
) - 1
RETURN
IF (
_D > 1
|| _D < -0.9,
BLANK (),
_D
/ COUNTROWS (
FILTER (
ALL ( 'Fact table' ),
'Fact table'[Product] = EARLIER ( 'Fact table'[Product] )
)
)
)
``````

I modify my sample data and it can get correct result. I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

Overall I can confirm that proposed solution worked also in real life with bigger data. Also the filtering worked very well. Thanks for the clever solution.

Maybe two notes /additional questions, if I may.:

1. I realised that "Sales weighted Price change" did not calculated right figure for all (products/catecories). In original sample result for all should be 12%, not 9% which seems to be just summarizing together category price changes without taking into account  category % of sales.

2. Is there way to calculate "Sales weighted Price change" index for other dimensions, like per country, per customer, etc. I do not have those dimensions in sample data. In other words, is there possibility to have "Sales weighted Price change" which work in filter context as such. This might be too complicated. Announcements #### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day! #### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference. Top Solution Authors
Top Kudoed Authors
Users online (3,258)