Forum Discussion

javif_84's avatar
javif_84
Helper 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...
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

javif_84's avatar
javif_84
Helper 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
      Helper 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 

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        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,

        Vinay Pabbu