Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Average of Measure

Hi,  In my historical project table I have created the following measure: TimeSinceModification = VAR _CurrentDate = TODAY() VAR _LastIdModification =     CALCULATE(             MAX(...
DAX
power bi
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Thank you for your message, and please check the below picture and the attached pbix file whether it suits your requirement.

     

     

    Expected avg measure: =
VAR _maxdatebyIDtable =
    CALCULATETABLE (
        Data,
        TREATAS (
            GROUPBY (
                Data,
                Data[ID],
                "@maxdate", MAXX ( CURRENTGROUP (), Data[Name ModifiedOn] )
            ),
            Data[ID],
            Data[Name ModifiedOn]
        )
    )
RETURN
    DIVIDE (
        SUMX ( _maxdatebyIDtable, Data[Days since modification] ),
        COUNTROWS ( _maxdatebyIDtable )
    )
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

Hi,

I am not sure how your dataset looks like, but please try something like below and check whether it works for your dataset.

 

expected measure: =
AVERAGEX (
    ADDCOLUMNS (
        VALUES ( 'projecttable'[projectID] ),
        "@datediff", [TimeSinceModification]
    ),
    [@datediff]
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Jihwan,

    I have tried to replicate the structure of my data tabel and hope it gives better meaning now:

    E.g. you can see that I have filtered 6 unique project. But project 260 have 2 different modification date and it should only calculate the Lates modified. 
    So here the correct Average should be (roundet) 5

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

      Hi,

      Thank you for your message, and please check the below picture and the attached pbix file whether it suits your requirement.

       

       

      Expected avg measure: =
VAR _maxdatebyIDtable =
    CALCULATETABLE (
        Data,
        TREATAS (
            GROUPBY (
                Data,
                Data[ID],
                "@maxdate", MAXX ( CURRENTGROUP (), Data[Name ModifiedOn] )
            ),
            Data[ID],
            Data[Name ModifiedOn]
        )
    )
RETURN
    DIVIDE (
        SUMX ( _maxdatebyIDtable, Data[Days since modification] ),
        COUNTROWS ( _maxdatebyIDtable )
    )
      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi, 

        I have implemented your solution and it works perfectly.

        Thanks