Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ikavaju
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.

CategoryProductsales datePriceQTYSales
TOOLSProduct A1.1.202110110
TOOLSProduct A1.8.202112224
TOOLSProduct B1.3.202113113
TOOLSProduct B1.10.202115115
APPLIANCESProduct C1.1.202118236
APPLIANCESProduct C1.4.202115345
APPLIANCESProduct D10.1.20219436
APPLIANCESProduct D12.2.20215210

 

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

CategoryProductFirst sales priceLast sales pricePrice change over timeSales% of category sales
TOOLSProduct A101220 %3455 %
TOOLSProduct B131515 %2845 %
APPLIANCESProduct C1815-17 %8164 %
APPLIANCESProduct D95-44 %4636 %

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)

 

CategorySales Weighted Price ChangeSales% of total
TOOLS18 %6233 %
APPLIANCES-27 %12767 %
  189 
 Sales Weighted Price Change  
TOTAL-12 %189 

 

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

 

 

1 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.

vkalyjmsft_0-1651138091108.png

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.

View solution in original post

5 REPLIES 5
v-yanjiang-msft
Community Support
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.

vkalyjmsft_0-1650943837328.png

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.

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 🙂

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!  

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.

vkalyjmsft_0-1651138091108.png

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.

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors