Forum Discussion
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...
// 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] ) )
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...
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/