Forum Discussion

PaulMcDk's avatar
PaulMcDk
Frequent Visitor
5 years ago
Solved

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

 

  • Anonymous's avatar
    Anonymous
    5 years ago
    // 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] )
)

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago
    // 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] )
)
    • PaulMcDk's avatar
      PaulMcDk
      Frequent Visitor
      5 years ago

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

       

       
      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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's avatar
    jthomson
    Solution Sage
    5 years ago

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

    • PaulMcDk's avatar
      PaulMcDk
      Frequent Visitor
      5 years ago

      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 .