Forum Discussion

julsr's avatar
julsr
Icon for Continued Contributor rankContinued Contributor
1 year ago
Solved

Average of values by clientcode

Hello everyone,   I have a dataset where I have a value for each Consumer Code that is duplicated for each ID I have on my database. I want to return the unique Amount value for each Code and Each ...
Need Help
Show and Tell
Tips and Tricks
  • ryan_mayu's avatar
    ryan_mayu
    1 year ago

    julsr 

    maybe you can try to create a new column to get the average first

     

    Column = DIVIDE('Table'[AMOUNT], countx(FILTER('Table','Table'[CODE]=EARLIER('Table'[CODE])&&year('Table'[Date])=year(EARLIER('Table'[Date]))&&month('Table'[Date])=month(EARLIER('Table'[Date]))),'Table'[CODE]))
     
Bibiano_Geraldo's avatar
Bibiano_Geraldo
Icon for Super User rankSuper User
1 year ago

Hi julsr ,

Try to modify you measure to something like this:

YearToDateAverage =
CALCULATE(
    SUMX(
        SUMMARIZE(
            FILTER(
                DATA,
                DATA[DATE] <= MAX(DATA[DATE])
            ),
            DATA[CODE],
            "UniqueAmount", AVERAGEX(
                FILTER(
                    DATA,
                    DATA[CODE] = EARLIER(DATA[CODE]) &&
                    DATA[DATE] <= MAX(DATA[DATE])
                ),
                DATA[AMOUNT]
            )
        ),
        [UniqueAmount]
    ),
    REMOVEFILTERS(
        DATA[DATE],
        DATA[YEAR],
        DATA[DATE_MONTH]
    )
)


Dont forget to replace table and column names with your owns.

 

Thank you