Average by Field bases on other field value

[SofiaVaz]

Good morning,

I'm quite new on DAX so I was wondering if someone can help me solving this issue…

Business: maritime public passenger transportation.

The DB is organized by Booking code that has a sailingcode associated. Every line of this DB is a passenger (a Booking can handle more than one passenger). The diferrent bookings for the same sailing have the same Sailing Code. Another field of this DB is the Ship name associated to the sailing of the booking.

What I'm trying to know is the average number of passanger transported by each ship, in the total of sailings performed in a certain period.

To calculate the total number of passangers in a certain period or from origin x to destination y, we have a measure that count all de bookings that are "CONFIRMED" in the status field.

I've already tried to create a measure with this expression

Ocupação Média por Navio(UnderConst) = AVERAGEX(VALUES(vw_Report_REPLICA_Invoices_Details[VesselName]);SUM(vw_Report_REPLICA_Invoices_Details[NumPassageiros]))

But it didn't worked…

Do you have any ideia what I might be doing wrong?

Thank you,

[v-eachen-msft]

Hi @SofiaVaz ,

Has your problem been solved? Was my reply helpful to you? If the problem is resolved, you could accept the reply you like.

[v-eachen-msft · Accepted solution]

Hi @SofiaVaz ,

Try the following DAX:

Ocupação Média por Navio(UnderConst) =
AVERAGEX (
    ALLEXCEPT (
        vw_Report_REPLICA_Invoices_Details;
        vw_Report_REPLICA_Invoices_Details[VesselName]
    );
    SUM ( vw_Report_REPLICA_Invoices_Details[NumPassageiros] )
)

If it doesn't work, you could share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to OneDrive for Business and share the link here.

[amitchandak]

@SofiaVaz, Try Like

Ocupação Média por Navio(UnderConst) = AVERAGEX(summarize(vw_Report_REPLICA_Invoices_Details;
vw_Report_REPLICA_Invoices_Details[VesselName];"_sum";SUM(vw_Report_REPLICA_Invoices_Details[NumPassageiros]));[_sum])

[SofiaVaz]

Hi amitchandak, thank for your help. Unfortunatly this expression output is the same of the one I had before...