Forum Discussion

TiemenHarms's avatar
TiemenHarms
Icon for Advocate I rankAdvocate I
4 years ago
Solved

Weighted avreage with dynamic What if parameter

Hello, 

 

I want to calculate a weighted average with a dynamic What if parameter used in a measure. I have the following syntax

 

HT average dynamic period =
AVERAGEX (
    DATESBETWEEN (
        'Date'[Date],
        MAX ( 'Date'[Date] ) - [Waarde van Parameter],
        MAX ( 'Date'[Date] )
    ),
    'Fact table'[Average handling time]
)

 

I have attached a simple pbix file with a dummy data of the model. I want to show the HT dynamic periode weighted bij the number of calls. 

 

https://1drv.ms/u/s!AouR1HCINaf0hKxpkzqauBaNEr9OZA

 

Does anybody know what Dax syntax i need to wrap around this to make it a weighted average?

 

Thank you. 

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    I'm assuming you want to weight by the number of calls rather than the number of days.

    HT average dynamic period =
VAR CurrDate = MAX ( 'Date'[Date] )
VAR StartDate = CurrDate - [Waarde van Parameter] + 1
RETURN
    CALCULATE (
        [Average handling time],
        DATESBETWEEN ( 'Date'[Date], StartDate, CurrDate )
    )

4 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

    TiemenHarms try this measure

     

    HT average dynamic period v1 = 
VAR __endDate = MAX ( 'Date'[Date] ) 
VAR __startDate = __endDate - ( [Waarde van Parameter] - 1 )
RETURN
AVERAGEX (
    DATESBETWEEN (
        'Date'[Date],
        __startDate,
        __endDate
    ),
    'Fact table'[Average handling time]
)

     

    ✨ Follow us on LinkedIn

     

    Learn about conditional formatting at Microsoft Reactor

    My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • TiemenHarms's avatar
      TiemenHarms
      Icon for Advocate I rankAdvocate I
      4 years ago

      Hello parry2k ,

       

      Thank you for your reply. The syntax looks almost similar to the one Alexis posted but does not shows the correct weighted average bij the number of calls (i forgot to mention this in my original post). 

       

      Thanks again for your time and help.

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    I'm assuming you want to weight by the number of calls rather than the number of days.

    HT average dynamic period =
VAR CurrDate = MAX ( 'Date'[Date] )
VAR StartDate = CurrDate - [Waarde van Parameter] + 1
RETURN
    CALCULATE (
        [Average handling time],
        DATESBETWEEN ( 'Date'[Date], StartDate, CurrDate )
    )
    • TiemenHarms's avatar
      TiemenHarms
      Icon for Advocate I rankAdvocate I
      4 years ago

      Hello AlexisOlson 

       

      Thank you for your reply. Yes I want to weight by the number of calls. The syntax you suggested shows the correct weighted average of Handling time.

       

      Thank you!