Calculate average based on column values and selected value in slicers

[Anonymous]

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.

[v-yanjiang-msft]

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 · Accepted solution]

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]

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

[amitchandak]

@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]

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?