Calculate a measure depending on value choosen by the slicer

[Anonymous]

Hi everyone,

I have a table that is giving me the "Down %" for each day. Down means that my production was stopped.

For example, on Day 1, Down% is equal to 0,27. Which means that, because I have 24h in a day, my production was stopped for 0,27*24 = 6,48 hours.

On my PBI I had slicers:

I'd like to calculate a measure that is giving me the number of hours as calculated previously but, for example, when my slicer is selecting a whole week, I'd need to have the average of Down% multiplied by the total of hours in a week. Same for a year for example or just a day and so on. I don't know how I can have a measure that can calculate this based on the selection of my slicers because if it was only one day I could just take Down% * 24hours but the number of hours here depend on what I selected so from 24hours it can be 168 for a week and so on ...

[Anonymous · Accepted solution]

Hi @Anonymous ,

To achieve this, you can use a combination of DAX measures that dynamically calculate the total hours based on the slicer selection and then apply the average Down% to this total. Here's a step-by-step approach to create such a measure:

Calculate Total Hours: First, you need a measure to calculate the total hours in the selected period. Assuming you have a Date table that is related to your main table, you can create a measure like this:
Total Hours = COUNTROWS('Date') * 24
This measure counts the number of days in your slicer selection and multiplies it by 24 to get the total hours.

Calculate Average Down%: Next, you need to calculate the average Down% for the selected period. This can be a simple average measure if you already have Down% calculated for each day:
Average Down% = AVERAGE('YourTable'[Down%])
Replace 'YourTable' with the actual name of your table.

Calculate Downtime Hours: Finally, you can calculate the downtime hours by multiplying the Total Hours by the Average Down%:
Downtime Hours = [Total Hours] * [Average Down%]
This measure will dynamically calculate the total downtime hours based on the slicer selection.

Please ensure that your slicers are correctly connected to your Date table and that the relationships in your model support these calculations.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous , thanks a lot it's super clear and efficient!!