Problem weighted average

[Capoccetta]

Dear all,

I’m new in power BI and I have a problem for calculating a weighted average based on a total of a column.

In attach a simplified example for my PBIX.

As you can see I have two table, a master table and a summarize table, with few fields calculated from master table. On summarized table I have a drivermonth field that represent the key of the tables, a field that represent sum of sales for driver, and distinct count number of deliveries for driver.

On the report I have a page filter where I can filtered the name of driver.

I want to calculate a weighted average, I’m trying with different formulas but all give me a wrong result because all calculation are carried out on the total number of the delivery, 21 and not only on the delivery filtered…

The formula used for SalesxDeliveries/TotalDeliveries is

SalesxDeliveries/TotalDeliveries = (Compatti[Delivery]*Compatti[Sales])/(SUM(Compatti[Delivery]))

I’m trying also with filter but without results

This is the total records

This is filtered records

In the last column the formula consider the total number of deliveries 21 instead of filtered dynamically 18

That is the result that I expect

Please give me a tip.

Thanks

[v-ljerr-msft · Accepted solution]

Hi @Capoccetta,

According to your descriptions above, it seems that you're using the formulas to create calculate columns to calculate SalesxDeliveries/TotalDeliveries, right?

Not like measures, calculate columns/tables are computed during database processing(e.g. data refresh) and then stored in the model, they do not response to user selections on the report.

So you should create a new measure(not column) to calculate SalesxDeliveries/TotalDeliveries in your scenario. And the formula below is for your reference. 

SalesxDeliveries/TotalDeliveries =
DIVIDE (
    SUMX ( Compatti, Compatti[Delivery] * Compatti[Sales] ),
    CALCULATE ( SUM ( Compatti[Delivery] ), ALLSELECTED ( Compatti ) )
)

Regards

[Capoccetta]

@v-ljerr-msft

it's work fine....

Thanks to all

[miltenburger]

Hi @Capoccetta

Maybe you can try the next measure:

SalesxDeliviries = Compatti[Delivery] * Compatti[Sales]

SalesxDeliviries/TotalDeliviries = DIVIDE(SalesxDeliviries, SUM(Compatti[Delivery])

So make 2 different measures, let me know if it works