Forum Discussion
Reference current filtered measure as a filter in another measure
Hello! I feel like this should be easy but I am new and stuck, your help is much appreciated!!!
I have a measure that calcuates the mode price (most recurring number) on a Sales fact table. I need to calculate the Average Units Sold when the Price Per Unit column equals the Mode Price based on the filtered product and customer selection.
Mode Price = VAR myTable = SUMMARIZE('Sales Fact','Sales Fact'[Price Per Unit],"Count",COUNT('Sales Fact'[Price Per Unit]))
VAR myTable2 = FILTER(myTable,[Count]=MAXX(myTable,[Count]))
VAR Mode1 = MAXX(LASTNONBLANK(myTable2,[Price Per Unit]),[Price Per Unit])
RETURN Mode1
VAR myTable2 = FILTER(myTable,[Count]=MAXX(myTable,[Count]))
VAR Mode1 = MAXX(LASTNONBLANK(myTable2,[Price Per Unit]),[Price Per Unit])
RETURN Mode1
Average Units Sold Mode Price = CALCULATE(AVERAGE('Sales Fact'[UNIT_SALES]), FILTER('Sales Fact','Sales Fact'[Price Per Unit]=[Mode Price]))
When I do this, it just calculates the average units. How do I pass the actual value of the mode to this filter?
Sales Fact
| CUSTOMER_ID | PRODUCT_ID | DATE | UNIT_SALES | Price Per Unit |
| 71570 | 1036 | 1/7/2018 | 1 | 4.99 |
| 71570 | 1036 | 1/14/2018 | 4 | 4.99 |
| 71570 | 1036 | 1/21/2018 | 3 | 4.99 |
| 71570 | 1036 | 1/28/2018 | 7 | 4.56 |
| 71570 | 1036 | 2/4/2018 | 4 | 4.99 |
| 71570 | 1036 | 2/11/2018 | 4 | 4.99 |
| 71570 | 1036 | 2/18/2018 | 2 | 4.99 |
| 71570 | 1036 | 2/25/2018 | 3 | 4.99 |
| 71570 | 1036 | 3/4/2018 | 6 | 4.99 |
| 71570 | 1036 | 3/11/2018 | 2 | 4.99 |
| 71570 | 1036 | 3/18/2018 | 5 | 3.00 |
In this example, the mode correctly returns 4.99
But the Average Units Sold Mode Price measure just returns the average of 3.72
The correct answer should be 3.22
I should also mention in my Report, I have two slicers for Customer_ID = 71570 and Product_ID = 1036.
3 Replies
- Ashish_Mathur
Super User
Thank you so much Ashish_Mathur !! This worked! I also wanted to create a measure for the Average Unit Sales when the price is not the mode. Would you be able to help with that?
- Ashish_Mathur
You are welcome. If my previous reply helped, please mark it as Answer. Try this measure
Average units sold at non mode Price = AVERAGEX(FILTER(SUMMARIZE(VALUES(Data[Price Per Unit ]),Data[Price Per Unit ],"ABCD",COUNTROWS(Data),"EFGH",MIN(Data[Price Per Unit ]),"IJKL",AVERAGE(Data[UNIT_SALES])),[ABCD]<>MAXX(SUMMARIZE(VALUES(Data[Price Per Unit ]),Data[Price Per Unit ],"PQRS",COUNTROWS(Data)),[PQRS])),[IJKL])
Hope this helps.