Forum Discussion
average dax
Hello to all,
I would like to make weekly and monthly averages of train punctuality in percentage.
Excel does it very well (screen view in pj) but I think it should be possible to do it in power bi desktop.
Every weekend I want power bi to divide the total number of non-punctual trains by the total number of trains to feed a percentage gauge.
I have tried different DAX formulas and measurements but I can't get it right. Would you have a formula to do this calculation?
thank you
Hi, Anonymous
Thank you for your posting, and I want to share how I proceed.
1. create custom-date table -> assign as a date table
2. create a relationship with Data table
3. Write the below three measures to calculate the percentage.
4. please check the picture if it is what you are looking for.
please also check the link down below, that is the sample pbix file
Ponctuels weekly total =VAR allweek =FILTER (ALL ( dates ),dates[ISO Weeknumber] = SELECTEDVALUE ( dates[ISO Weeknumber] ))VAR allweektotal =CALCULATE ( SUM ( Data[Ponctuels] ), allweek )RETURNIF ( SELECTEDVALUE ( dates[DayOfWeek] ) = 0, allweektotal, BLANK () )Trains weekly total =VAR allweek =FILTER (ALL ( dates ),dates[ISO Weeknumber] = SELECTEDVALUE ( dates[ISO Weeknumber] ))VAR allweektotal =CALCULATE ( SUM ( Data[Trains] ), allweek )RETURNIF ( SELECTEDVALUE ( dates[DayOfWeek] ) = 0, allweektotal, BLANK () )Ponctuels by Trains =DIVIDE( [Ponctuels weekly total], [Trains weekly total], BLANK())Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
2 Replies
Hi Anonymous ,
You can use a DAX something like below:
If you want to create a column then use below and chnage the datatype to percentage:
calc = DIVIDE([Total number of non punctual trains], [Total number of trains], 0)
If you want to create a measure, then use the following:
calc = DIVIDE(SUM([Total number of non punctual trains]), SUM([Total number of trains]), 0)
Just use your original columns and tablename in the above dax calculations.
Thanks,
Pragati
Hi, Anonymous
Thank you for your posting, and I want to share how I proceed.
1. create custom-date table -> assign as a date table
2. create a relationship with Data table
3. Write the below three measures to calculate the percentage.
4. please check the picture if it is what you are looking for.
please also check the link down below, that is the sample pbix file
Ponctuels weekly total =VAR allweek =FILTER (ALL ( dates ),dates[ISO Weeknumber] = SELECTEDVALUE ( dates[ISO Weeknumber] ))VAR allweektotal =CALCULATE ( SUM ( Data[Ponctuels] ), allweek )RETURNIF ( SELECTEDVALUE ( dates[DayOfWeek] ) = 0, allweektotal, BLANK () )Trains weekly total =VAR allweek =FILTER (ALL ( dates ),dates[ISO Weeknumber] = SELECTEDVALUE ( dates[ISO Weeknumber] ))VAR allweektotal =CALCULATE ( SUM ( Data[Trains] ), allweek )RETURNIF ( SELECTEDVALUE ( dates[DayOfWeek] ) = 0, allweektotal, BLANK () )Ponctuels by Trains =DIVIDE( [Ponctuels weekly total], [Trains weekly total], BLANK())Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
