Problem with weighted average formula

[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 tips....

Thanks

[Anonymous]

@Capoccetta,

Create a measure using DAX below.

Measure = SUM(Compatti[Sales])*SUM(Compatti[Delivery])/CALCULATE( SUM(Compatti[Delivery]),ALLSELECTED(Compatti[Driver]))


Regards,
Lydia

[Capoccetta]

This is my PBIX https://1drv.ms/u/s!AqfsJnD6F-2ggdhEmH0ydA3nfpuHLQ

It's the same formula that I'm checking

Measure1 = SUM(Compatti[Sales])*SUM(Compatti[Delivery])/CALCULATE(SUM(Compatti[Delivery]);ALLSELECTED(Compatti[DriverMonth]))

but this is my result.... It's no correct...

[Anonymous]

@Capoccetta,

You would need to create the following measures in the sheet1 table and create a new table visual as folllows.

SalesxDelivery = SUM(Compatti[Sales])*SUM(Compatti[Delivery])
AllDelivery = CALCULATE(SUM(Compatti[Delivery]),ALLSELECTED(Sheet1[Driver]),ALL(Sheet1[DriverMonth]))
ExpectedMeasure = [SalesxDelivery]/[AllDelivery]



Regards,
Lydia

[Capoccetta]

@Anonymous

Tks Lydia for your attention...

Only a thing.... The meaure work correctly but the total of the column it's wrong.

[Anonymous]

Dear @Capoccetta,

Can you share again your pbix again, I'll make formular and check on it.

Regards,

ManNVSM

[Capoccetta]

@Anonymous

This last pbix modified

https://1drv.ms/u/s!AqfsJnD6F-2ggdhFCiMqkwzPACHG-w

Thanks

[Anonymous]

Dear @Capoccetta,

I'm just realize that your Formular is not good.

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

With GrandTotal, it value will = Sales.

Example: (A*B)/A = B. Because A/A = 1.

So (1*B) =B. In this case, A = Delivery, B = Sale. Right?

You can see a picture below. So, you can give me more about your request, maybe we find another way to solve it.

[Capoccetta]

Dear @Anonymous

It's all correct... I thought the same

The aim is to calculate for every item the weighted average and to have a correct total of the relatively column.

[Anonymous]

Dear @Capoccetta,

Yup.

Now Table can not let us choose what will for Grandtotal. Maybe sum from the detail or maybe from Formular (default). Hope it comming soon by Idea.

If you need a hand on the other issue, just tag my account in your topic 🙂

Btw, if you thing what reply is solution, please choose it to close this Topic.

Regards,

ManNVSM.

[Capoccetta]

Dear @Anonymous

the solution is to have the Expected Measure Formula and the same results on a column formula, one or more columns.

One column with detail results and another column with a total of selected records which can I use as grandtotal...

I'm cheking it but now without results...

In your opinion based on your experience Is it possibile?

[Vvelarde]

@Capoccetta

Hi, try this:

Modify:

AllDelivery =
CALCULATE (
    SUM ( Compatti[Delivery] ),
    ALLSELECTED ( Sheet1[Driver] ),
    ALLSELECTED ( Sheet1[DriverMonth] )
)

And create a new measure

ExpectedMeasure VV =
IF (
    HASONEVALUE ( Sheet1[Driver] ),
    [ExpectedMeasure],
    SUMX (
        SUMMARIZE (
            Sheet1,
            Sheet1[Driver],
            Sheet1[DriverMonth],
            "AA"; [SalesxDelivery] / [AllDelivery]
        ),
        [AA]
    )
)

You can Change the AA to another name and replace expected measure with the code.

Regards

Victor

Lima - Peru

[Capoccetta · Accepted solution]

with this formula works fine

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

Thanks to all....