Sum values in matrix that is higher than x

[Anonymous]

Good day

I'm new to PBI and trying to replicate an excel report on to PBI. 

I managed to replicate most information but now I'm stuck on one value I want to display. I searched this forum and there is something similar but not completely what I'm looking for. Unless I didn't look hard enough and can be pointed in the right direction. 

I have a matrix that is displaying average values per hour per day. I have a date slicer whereby the user can filter on a specific date. When the (average) value is equal to 1 or more I have a conditional format in the matrix to highlight it. But now I'm looking for a way to sum only those values to get to a total. 

For example below all the branches would have a total of zero but the Centurion Lifestyle Centre must have a total of 4.5

In excel it was easy with a SumIF.

The reason why I'm using averages is because of the raw data. The data is provided per 15-minute interval and there are different queues. There are 8 rows of data per hour and an average is then required.

Below is sample data for one day for Centurion Lifestyle Centre.

The column that is used for the average on the matrix is called "Staff Over/Short"

I removed some hours as the post exceeded the character limit.

Date	Week	Day	Hour	Staff Over/Short	Q Name	branch code
2022/05/31	23	Tuesday	8	0	Deposit & Enquire	2287
2022/05/31	23	Tuesday	8	0	Deposit & Enquire	2287
2022/05/31	23	Tuesday	8	0	Deposit & Enquire	2287
2022/05/31	23	Tuesday	8	0	Deposit & Enquire	2287
2022/05/31	23	Tuesday	8	3	One to One	2287
2022/05/31	23	Tuesday	8	3	One to One	2287
2022/05/31	23	Tuesday	8	4	One to One	2287
2022/05/31	23	Tuesday	8	4	One to One	2287
2022/05/31	23	Tuesday	15	0	Deposit & Enquire	2287
2022/05/31	23	Tuesday	15	0	Deposit & Enquire	2287
2022/05/31	23	Tuesday	15	0	Deposit & Enquire	2287
2022/05/31	23	Tuesday	15	0	Deposit & Enquire	2287
2022/05/31	23	Tuesday	15	2	One to One	2287
2022/05/31	23	Tuesday	15	3	One to One	2287
2022/05/31	23	Tuesday	15	2	One to One	2287
2022/05/31	23	Tuesday	15	3	One to One	2287
2022/05/31	23	Tuesday	16	0	Deposit & Enquire	2287
2022/05/31	23	Tuesday	16	0	Deposit & Enquire	2287
2022/05/31	23	Tuesday	16	0	Deposit & Enquire	2287
2022/05/31	23	Tuesday	16	0	Deposit & Enquire	2287
2022/05/31	23	Tuesday	16	3	One to One	2287
2022/05/31	23	Tuesday	16	3	One to One	2287
2022/05/31	23	Tuesday	16	3	One to One	2287
2022/05/31	23	Tuesday	16	3	One to One	2287

Thank you for your time.

[v-zhangti · Accepted solution]

Hi, @Anonymous 

According to the data you provided you can try the following methods.

Measure:

Average = 
CALCULATE (
    AVERAGE ( 'Table'[Staff Over/Short] ),
    FILTER (
        ALL ( 'Table' ),
        [Hour] = SELECTEDVALUE ( 'Table'[Hour] )
            && [Q Name] = SELECTEDVALUE ( 'Table'[Q Name] )
    )
)

Color = IF([Average]>=1,"Green",BLANK())

By putting the Color measure into the conditional format of the average, you can highlight the values greater than or equal to 1.

Sum = 
SUMX (
    FILTER (
        SUMMARIZE ( 'Table', 'Table'[Q Name], 'Table'[Hour], "Aver", [Average] ),
        [Aver] >= 1
    ),
    [Aver]
)

Best Regards,

Community Support Team _Charlotte

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

[Anonymous]

Thank you very much @v-zhangti!

I learned a lot today and your solution is spot on!