Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
Recently I'm confuse oh using AVERAGE or AVERAGEX function.
For example I have this Sales data (at first) :
SalesID | SalesTeritory | OrderDate | Amount |
SO-01 | EAST | 1/1/2019 | 500 |
SO-02 | EAST | 1/1/2019 | 100 |
SO-03 | WEST | 1/1/2019 | 200 |
SO-04 | WEST | 1/3/2019 | 300 |
SO-05 | WEST | 2/1/2019 | 100 |
I create a simple measure for the Total amount :
[Total amount] = SUM(Sales[Amount])
Then there is requirement which I need to join to some table and it product some duplicate row because of the join.
SalesID | SalesTeritory | OrderDate | Amount | Location |
SO-01 | EAST | 1/1/2019 | 500 | Main W/H |
SO-02 | EAST | 1/1/2019 | 100 | Main W/H |
SO-03 | WEST | 1/1/2019 | 200 | Main W/H |
SO-03 | WEST | 1/1/2019 | 200 | Aisle 2 |
SO-04 | WEST | 1/3/2019 | 300 | Main W/H |
SO-05 | WEST | 2/1/2019 | 100 | Main W/H |
I need my Total Amount measure handle this duplicate rows, so I thought average between these duplicate value will do the trick, but apperntly not.
At first I created this addtional measure :
Do I need to use COUNTROWS and use DIVIDE instead ?
Kindly advise,
Thanks
Solved! Go to Solution.
If you add all column on the table visual, please create a measure
Measure = CALCULATE(AVERAGE('Table'[Amount]),ALLEXCEPT('Table','Table'[SalesID],'Table'[OrderDate],'Table'[SalesTeritory]))
If you only add SalesID, SalesTeritory, OrderDate, Amount on the table, just create the measure
Measure 2 = AVERAGE('Table'[Amount])
If you add all column on the table visual, please create a measure
Measure = CALCULATE(AVERAGE('Table'[Amount]),ALLEXCEPT('Table','Table'[SalesID],'Table'[OrderDate],'Table'[SalesTeritory]))
If you only add SalesID, SalesTeritory, OrderDate, Amount on the table, just create the measure
Measure 2 = AVERAGE('Table'[Amount])
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |