Forum Discussion

ckhoo2007's avatar
ckhoo2007
Icon for Helper I rankHelper I
5 years ago
Solved

AverageX with Date Slicer

Hi All,

I have a sample data as follow,

DateClientAverage SalesTransactions
4/5/2021A811.535
3/5/2021A705.089
29/4/2021B701.981
3/5/2021C581.452
7/5/2021A570.2919
5/5/2021A555.5617
28/3/2021D552.591
28/3/2021E547.891

 

 

I wanted to calculate for each client the average of (Average Sales * Transactions)/ Sum of Transactions for a certain range of date based on a date slicer, I have tried:

 

DIVIDE(AVERAGEX(Sales,Sales[Average Sales]*Sales[Transactions]),SUM(Sales[Transactions]))

 

but it doesn't seems to segragate by Client/ Date slicer.....

I already have a Date table with relationship created to the Sales table.

Appreciate your advise on the right formula, thanks.

 

Regards.

Choo

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    HI ckhoo2007,

    You can try to use the following measure formula if helps:

    Masure =
DIVIDE (
    AVERAGEX (
        SUMMARIZE (
            ALLSELECTED ( Sales ),
            [Date],
            [Client],
            "Rate", Sales[Average Sales] * Sales[Transactions]
        ),
        [Rate]
    ),
    SUM ( Sales[Transactions] )
)

    Regards,

    Xiaoxin Sheng

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HI ckhoo2007,

    You can try to use the following measure formula if helps:

    Masure =
DIVIDE (
    AVERAGEX (
        SUMMARIZE (
            ALLSELECTED ( Sales ),
            [Date],
            [Client],
            "Rate", Sales[Average Sales] * Sales[Transactions]
        ),
        [Rate]
    ),
    SUM ( Sales[Transactions] )
)

    Regards,

    Xiaoxin Sheng