Forum Discussion

javif_84's avatar
javif_84
Icon for Helper I rankHelper I
1 year ago
Solved

Average for a characteristic

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

 

 

Need Help
  • v-dineshya's avatar
    v-dineshya
    1 year ago

    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

9 Replies

  • javif_84's avatar
    javif_84
    Icon for Helper I rankHelper I
    1 year ago

    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
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        javif_84
        Icon for Helper I rankHelper I
        1 year ago

        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