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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.