Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculate average based on column values and selected value in slicers

Hi All,

 

I am working on calculating average for one of my dashboard, I want average to be calculated as selected value of slicer.

 

Let say I have one column of product type, another column of location and third is for week. I want my average to be calculated as selected value of this columns in slicer. If i am selecting product type the average will be based on that particular product type only. 

 

Currently I am using below formula:

 

Average OEE_1 = AVERAGEX(VALUES(Deliver_O[ProductType]), AVERAGEX(VALUES(Deliver_O[Location]), AVERAGEX(VALUES(Deliver_O[Week]), CALCULATE('Measure'[Average O]))))
 
This formula helped me in getting average for product type selection, location selection but not in week selection.
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi v-kalyj-msft,

     

    I am attaching the screenshot of my sample data in excel, When I am using simple average dax formula, I am getting correct results for first two tables in screenshot but when i am selecting multiple location in slicer(washinton+London) the value is not matching.

     

    I have attached both screen shot for excel and for PBI also

     

    ExcelPBI

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , try measure like

     

    Average OEE_1 = Switch(selectedvalue(Slicer[value),
    "Product Type", AVERAGEX(VALUES(Deliver_O[ProductType]), CALCULATE('Measure'[Average O])),
    "Location" , AVERAGEX(VALUES(Deliver_O[Location]), CALCULATE('Measure'[Average O])),
    "Week", AVERAGEX(VALUES(Deliver_O[Week]), CALCULATE('Measure'[Average O]))
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks Amit,

       

      In first line "slicer" is greyed out for me, I am not sure if I am doing something wrong or not.

       

      Those Product type, location in commas are to display something?

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

    According to your description, I create a sample, Average O is a measure.

    As you say, you want the measure dynamically change by the slicers of ProductType, Location and Week.

    However, as the measure created from Sales which is in the same table with the slicers, the measure can aotumatically change by the slicers without do anything. 

    If this is not the problem, could you please explain it more through my sample. I attach it below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi v-kalyj-msft,

       

      I am attaching the screenshot of my sample data in excel, When I am using simple average dax formula, I am getting correct results for first two tables in screenshot but when i am selecting multiple location in slicer(washinton+London) the value is not matching.

       

      I have attached both screen shot for excel and for PBI also

       

      ExcelPBI

      • v-yanjiang-msft's avatar
        v-yanjiang-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi Anonymous ,

        What's the formula of OEE measure? Why the average of (0.63264, 0.62643, 0.60761, 0.62469) is not 0.62167.

        Best Regards,
        Community Support Team _ kalyj