cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PaulMcDk
Frequent Visitor

Average cost with distinctcount

Dear community,

 

it's my very first post and i'm quite beginner to this Power BI .

 

I'm slowly building up a model with Price - Mix - Volume effect and i have two tables , 1 for actual data and 1 for budget data.

 

I need to calculate the average of an event (for example avg  actual cost from Belgio to Bulgaria and avg BUDGET cost from Belgio to Bulgaria) by taking into consideration the # of Trips done. I have alraedy the number of trip  (column Count Trip) for which i used the formula DISTINTCOUNT . 

 

Now what i have used to calculate the AVG cost (simply the ACT COST / Count Trip ) . For example first line should be 24.860 / 18 .

 

AVG Actual Cost = AVERAGEX('ACTUAL data', 'ACTUAL data'[ACT cost]/ [Count Trip]) but it does not work as i guess it is not considering the countdisting but all the number of lines behind .
 
somebody can help me ?
thank you very much
PaulMcDk_0-1626346338452.png

 

1 ACCEPTED SOLUTION
daxer
Solution Sage
Solution Sage

// If you want the average actual cost
// per trip in a given country averaged 
///across different countries, then:


AVG Actual Cost =
AVERAGEX(
	DISTINCT( 'Actual Data'[Tax Country Departure] ),
	// This DIVIDE gives you the average cost of
	// a trip in the currently iterated
	// Country of Departure and the AVERAGEX
	// makes sure that you average these averages
	// over all visible countries of departure.
	DIVIDE( [ACT cost], [Trip Count] )
)

View solution in original post

5 REPLIES 5
daxer
Solution Sage
Solution Sage

// If you want the average actual cost
// per trip in a given country averaged 
///across different countries, then:


AVG Actual Cost =
AVERAGEX(
	DISTINCT( 'Actual Data'[Tax Country Departure] ),
	// This DIVIDE gives you the average cost of
	// a trip in the currently iterated
	// Country of Departure and the AVERAGEX
	// makes sure that you average these averages
	// over all visible countries of departure.
	DIVIDE( [ACT cost], [Trip Count] )
)

View solution in original post

PaulMcDk
Frequent Visitor

O, it worked ! 

i had to "fine tuned" a bit and create a measure for Count Trip and add the SUM . 

 

BDG AVG Cost = AVERAGEX(
    DISTINCT( Masterdata[Tax Country Departure] ),
    // This DIVIDE gives you the average cost of
    // a trip in the currently iterated
    // Country of Departure and the AVERAGEX
    // makes sure that you average these averages
    // over all visible countries of departure.
    DIVIDE(sum('Budget 21/22'[BDG Cost]),[Count #Trip BDG]))
 
 
I have another question :  what is the line "TOTAL" doing ? I mean is not neither the total of line nor the average...
 
 
PaulMcDk_0-1626354156377.png

 

 

This is bad coding, @PaulMcDk. YOu should never precede a measure with the table it exists in and you should always do it with columns. The SUM in DIVIDE does nothing for you, and is indeed totally redundant (not to say: wrong). Here's the code as it should be:

 

 

BDG AVG Cost = 
AVERAGEX(
    DISTINCT( Masterdata[Tax Country Departure] ),
    DIVIDE(
    	[BDG Cost], -- this must be a measure, the sum of costs
    	[Count #Trip BDG]
    )
)

 

 

Please follow these guidelines religiously: https://www.sqlbi.com/articles/rules-for-dax-code-formatting/

 

jthomson
Solution Sage
Solution Sage

I'm struggling to see why simply divide([ACT cost],[Count Trip]) wouldn't work

nice question 🙂 i guess it is doing the average not by the COUNT TRIP but considering the total items present in the table . 

For 1 trip i can have more than a line cost .

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.