Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |