Complicated Weighted Average with Running Total

[Anonymous]

Hi there,

I am trying to calculate a weighted average, but all the approches in the community are a little to simple for my case... I hope there are some people who can help me.

Thats the scenario:

Currently selected Date: 2019

Each customer has a rating, but the last rating date might vary. Here's my DAX approch regarding my last ever non empty rating for each customer:

Last Ever Non Empty Rating = 
SUMX(
    ADDCOLUMNS(
        ADDCOLUMNS(
            VALUES('customer'[customer_id]) ;
            "Last Ever Non Empty Date";
            CALCULATE(
                MAX( fact_rating_table[date] ) ;
                calendar[year] <= VALUES( calendar[year] )
            )
        ) ;
        "Last Ever Non Empty Rating" ;
        CALCULATE (
            SUM( fact_rating_table[rating] ) ;
            FILTER( ALL(calendar) ; calendar[date] = [Last Ever Non Empty Date] )
        )
    ) ;
    [Last Ever Non Empty Rating]
)

Here's my DAX approach for calculating the running total:

Running Total = 
var MaxDate = Max ( calendar[date] )
RETURN
    CALCULATE(
        Sum('fact_contract'[amount_per_transaction]);
        calendar[date] <= MaxDate
    )

As you can see, the running total is calculated by another fact table ("fact_contract" - customer <==> contract = 1:n). Both fact tables are joined both by the customer and the date dimension.

So here's my goal:

I want to calculate the weighted average for the rating for any given dimension (e.g. "industry sector" - joined to both fact tables) as shown above (==> 6,9 weighted avg instead of 5,7 'normal' avg) for any given date.

Calculation:

[Last Ever Non Empty Rating] * [Running Total] (for each customer) / [Running Total] (of all customers respecting the current filter context)

Many thanks in advance.

Axel

[Anonymous · Accepted solution]

I have not tried this in my own workbook, but you can try to use SUMX because it will iterate across all rows in a table (it can though be slow if you have many rows). This way you should be able to use other filters like 'sector'.

Weighted average = SUMX( Customer; [Last Ever Non Empty Rating] * [Running total]) / [Running total]

If this works then please mark it as the solution. Kudos is also appreciated 

[Anonymous]

Thought it would not work because PBI did no intellisense on the names initially ...
But obviously sometimes it is simple (not in my mind at the beginning anyway).

Thanks for that.