topic Re: Calculate average based on column values and selected value in slicers in DAX Commands and Tips

Calculate average based on column values and selected value in slicers

Anonymous — Wed, 06 Jul 2022 13:38:42 GMT

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.

Re: Calculate average based on column values and selected value in slicers

amitchandak — Wed, 06 Jul 2022 16:23:55 GMT

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

Re: Calculate average based on column values and selected value in slicers

Anonymous — Wed, 06 Jul 2022 16:48:05 GMT

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?

Re: Calculate average based on column values and selected value in slicers

v-yanjiang-msft — Wed, 13 Jul 2022 03:03:09 GMT

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.

Re: Calculate average based on column values and selected value in slicers

Anonymous — Wed, 13 Jul 2022 06:30:00 GMT

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

Re: Calculate average based on column values and selected value in slicers

v-yanjiang-msft — Wed, 13 Jul 2022 09:51:22 GMT

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