Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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 )
    RETURN
    IF ( 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 )
    RETURN
    IF ( 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!!
Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

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 )
RETURN
IF ( 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 )
RETURN
IF ( 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!!