Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 .
Solved! Go to Solution.
// 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] )
)
// 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 .
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/
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 .