Calculate average price based on certain condition

[Nun]

I have this table, 

Agent	PZ	EUR/pz
A	100	280
	50	275
C	220	260
D	130	258
E	300	255
F	500	253
G	80	252
H	20	240
I	300	239
J	700	238
K	750	235
L	50	225
M	60	220
N	40	215
O	200	210

PZ are calculated based on a formula sum (PC), EUR/pz is a formula too sum(eur)/sum(PC)
now I need to calculate the average price of highest prices falling within 10% of total PC. An example: in the table the total PC is 3500, the 10% is 350 so A,B,C are the top prices which have the pz amount within 10%, so 

we EUR 93750 (below table) PC 350 TOP EUR *PC 268 (93750/350),

PC x EUR/PC

28000

13750

52000

[v-tsaipranay]

hello

[Nun]

Hi.

Thanks so much for all. I will continue to find a solution by myself. I have contacted a partner support but the costs are not compatible with the request.

Thank you

[v-tsaipranay]

Hi @Nun ,

We did our best to assist you, but unfortunately, we were not able to achieve the desired result. Since you chose not to engage with Partner Support, we hope you are able to resolve the issue from your end.

If you have found a solution, we would appreciate it if you could share your experience with the community, as it may help others with similar issues.

According to our support policy, we will now close this thread. If you need further assistance in the future, please open a new thread and we will be happy to help.

Thank you for your understanding and collaboration.

[v-tsaipranay]

Hi @Nun ,

Since we have tried all possible solutions, I recommend you to raise a partner support request so they can assist you further with this issue.

Please use the link below to submit your request: Re: Calculate average price based on certain condi... - Page 2 - Microsoft Fabric Community

Thank you.

[v-tsaipranay]

Hi @Nun ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Nun]

Hi,
no I didn't. In the solution you proposed, it's not calculate the "remaining part". I try to figure out how to add it.

[Nun]

No, I didn't resolve the issue, in a post you were mentioning to create a table rank ( I remember correctly), but I cannot find that post.Thanks

[v-tsaipranay]

Hi @Nun ,

As I mentioned in my previous message, please click the link provided. It will take you to the post I referred to earlier about creating a table rank.

Thank you.

[v-tsaipranay]

Hi @Nun ,

Can you please confirm whether you have resolved issue. If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. This will be helpful for other community members who have similar problems to solve it faster. 

If we don’t hear back, we’ll go ahead and close this thread.Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you.

[Nun]

Hi,

unfortunately, I didn't solve the issue. We need to consider that EUR/PZ is a measure and not an existing column. We have the column EUR, PZ but not EUR/PZ. We have the same agent that sells to different delivery customer, the same product (or different) in the same quarter with different EUR and different quantity PZ. Creating the rank, ofcourse there several rows with the same rank. Based on your model, I created a column EUR/PZ, then a column Rank, using the column EUR/PZ, that a measure TopEUR, 10% and the TopAvg, but the result is so far to be correct.

Please take a look of this example for a selected product in Q2 2024 PZ and EUR are measure. The total PZ is 1202 for Q2, the calculated 10% is 120,2, 9761,07 is PZ*EUR/PZ. As end result what we need to get is the top price 345,49. Applying your formulas, I get for some reason, 363. Thanks!

[v-tsaipranay]

Hi @Nun , 

Thank you for your continued efforts in testing the approaches we’ve provided. As some time has passed and multiple solutions have been attempted without achieving the desired outcome, we kindly suggest raising a support ticket with the Microsoft product team. They will be able to investigate the matter further and provide more in-depth assistance to help resolve the issue.

You can raise a support ticket using the following link: Submit a product support request

Additionally, if you do find a resolution through the support channel, we would sincerely appreciate it if you could share your findings with the community. Your insights may help other members facing similar challenges.

Thank you for your patience and understanding. Please continue to engage with the Fabric Community for any further questions or support needs.

[v-tsaipranay]

Hi @Nun ,

We are following up once again regarding your query. Could you please confirm if the issue has been resolved through the support ticket?

If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.

Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you for your understanding and participation.

[Nun]

Hi,

unfortunately I cannot open a ticket. I have PRO license, but I guess in my organization we cannot open a ticket

I try to figure out how the get the cumulative, considering that I don't have as a column EUR/PZ but I have EUR and PZ column, and different Prices (EUR colum) for different quantities. Because the same agent can buy the same product, during a quarter, different times, with different prices. Ofcourse differnt agents can buy the same product with same price (EUR) and different PZ. SUM PZ is a measure EUR/PZ is a measure in our table. 

[v-tsaipranay]

Hi  @Nun, 

If you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi
Otherwise you can raise an issue a...https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues .

Pro ticket response time varies depending on your company's support contract.  First level support is a Microsoft partner who will contact you according to your stated preferences (phone/email) and will arrange for the initial triage call.

Thank you.

[v-tsaipranay]

Hi @Nun ,

Could you please confirm if the issue has been resolved after raising a support case using pro license? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.

If we don’t hear back, we’ll go ahead and close this thread. Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you for your understanding and participation.

[Nun]

Hello,
I opened a support request, I did not reply because the problem is being analysed to propose a solution. I will keep you updated

[Nun]

Hello, they didn't support me. They cannot give any supports to build a dax query. They suggested to contact a partner. 

[v-tsaipranay]

Hi @Nun ,

Thank you for the update, and I’m sorry to hear the support team couldn’t assist with the DAX query. As noted, support team does not provide help with building custom DAX expressions. In such cases, we recommend connecting with a certified BI specialized partner who can provide hands-on assistance. You can do this by visiting the Power BI Partners | Microsoft Power Platform, scrolling to the “BI specialized partners” section, and clicking “Learn more” to view and contact available partners. Please refer to the screenshot below for your reference:

Hope this helps. Please reach out for further assistance.

Thank you.

[Nun]

Hi, you have sent a message with a possible solution, creating a rank table, but I cannot find anymore that message. Please could you re-post it? 
Thanks!

[v-tsaipranay]

Hi @Nun ,

Thanks for the followup, please follow this link for the message where I suggested rank :
Re: Calculate average price based on certain condi... - Microsoft Fabric Community

Thank you.