Average for a characteristic

[javif_84]

Hi all

I would like to create a formula from excel in Power BI

Basically independantly from the row sales of the customer it gives the average of sales for the rating of the client considered. There are 4 ratings, so if there are two clients with the same rating they should have the same number 

To make it more complicated I just want it to make the average of the clients that have data anf if 0 exclude it

[javif_84]

Hi 

Thanks for the reply, true it is not clear

So see the dataset https://we.tl/t-dKuv7RRhyT 

So my DAX formula is

average = VAR CurrentRating = SELECTEDVALUE(Sales[Rating])

return

if(

    CurrentRating IN {"C1","C2","C3","C4"},

calculate(averagex(Sales,Sales[Units Sold]),

ALLEXCEPT(sales,Sales[Rating]),

Sales[Rating] in {"C1", "C2", "C3","C4"}))

It is not correct as it is making me the average for the field AM and this is not correct as I want te formula to give an average for each one of the ratings C1, C2, C3 and C4 independently of the customer buyer name, AM, ABM and all the other fields. And moreover I need to exclude all the rows with a blank value. See my example in the excel

I do not achieve that, I tried with analyst chatGTP and don t get it neither

Please help

Hope this clarifies

[v-vpabbu]

Hi @javif_84,

Thank you for reaching out to Microsoft Fabric Community Forum.

You can calculate the average using the following approach:

AVG = AVERAGEX(FILTER(Sheet2,Sheet2[Rating]IN {"C1","C2","C3","C4"}),Sheet2[Units Sold])

• Use a slicer for the Rating column to allow users to select categories dynamically.
• Display the result in a Card visual to showcase the average Units Sold for the selected ratings.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,
Vinay Pabbu

[javif_84]

Hi

It does not work as you can see the average of a C3 for a field AM/ABM is different to a C3 for another AM/ABM. What I would like is the all C3 have the same average independently of the AM/ABM selected 

[v-dineshya]

Hi @javif_84 ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards

[v-dineshya]

Hi @javif_84 ,

Could you provide sample data or a PBIX file? This will help us better understand your issue and guide you toward a solution.

Regards,

Dinesh

[v-vpabbu]

Hi @javif_84,

Could you provide sample data or a PBIX file? This will help us better understand your issue and guide you toward a solution.

Regards,

Vinay Pabbu

[v-vpabbu]

Hi @javif_84,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[parry2k]

@javif_84 hard to understand your request. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490