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

[ikavaju]

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.

[v-yanjiang-msft]

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.

[ikavaju]

Hi @v-yanjiang-msft ,

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 🙂

[ikavaju]

Hi @v-yanjiang-msft,

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"

Thanks for the help you can provide!  

[v-yanjiang-msft · Accepted solution]

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.

[ikavaju]

Hi @v-yanjiang-msft ,

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.