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.
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.
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 |
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
You may download my PBI file from here.
Hope this helps.
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?
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.
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 |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |