Reference current filtered measure as a filter in another measure

[Anonymous]

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

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.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

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.