cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculting the Maximum of a calculated sum depending on filtered data

I try to simplify my problem, I hope it's understandable:
I got a table with Columns Ticket_Id, User, Date, Hours. It should be possible to filter the table by Ticket_Id and date.
First, I want to calculate the sum of hours spent on all tickets a day while paying attention to the ticket filter.
Then, for each User, I want to calculate the maximum of that sum in the selected date period.

It would be no problem if I could just calculate the sum of hours a user spent on all tickets for each day in a calculated column. The problem is that this sum needs to depend on the ticket filter, which can be set at any time. Thus a measure is necessary to make it dynamic I think?
So far I would calculate the column

`SumT = CALCULATE(SUM(Abfrage1[Hours]);FILTER(ALLSELECTED(Abfrage1);Abfrage1[Date]=Earlier(Abfrage1[Date]) && Abfrage1[User]=EARLIER(Abfrage1[User]))) `

and then take the maximum of that.

`MaxT = CALCULATE(MAX(Abfrage1[SumT]);FILTER(ALLSELECTED(Abfrage1); Abfrage1[User]=EARLIER(Abfrage1[User])))`

The problem is that this sum does not take into calculation the tickets and dates that I use as a filter. Thus I think a measure is necessary but that doesn't allow the Earlier function.
In addition to all this, I also need to be able to use the MaxT value for every user as a filter criterium on a report page.

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: Calculting the Maximum of a calculated sum depending on filtered data

Hi @Elli91,

Could you try the formula below to see if it works in your scenario?

```Measure =
CALCULATE (
MAXX (
SUMMARIZE (
Abfrage1;
Abfrage1[User];
Abfrage1[Date];
"TotalHours"; SUM ( Abfrage1[Hours] )
);
[TotalHours]
);
ALL ( Abfrage1[Ticket_Id] )
)
```

Regards

5 REPLIES 5
Microsoft

## Re: Calculting the Maximum of a calculated sum depending on filtered data

Hi @Elli91,

If I understand you correctly, you should be able to use the formula below to create a new measure to calculate the MaxT, and then use the measure as Visual Level Filter for all visuals on your report to filter Users in your scenario.

```Measure =
MAXX (
SUMMARIZE (
Abfrage1;
Abfrage1[User];
Abfrage1[Date];
"TotalHours"; SUM ( Abfrage1[Hours] )
);
[TotalHours]
)
```

Regards

Frequent Visitor

## Re: Calculting the Maximum of a calculated sum depending on filtered data

Hi @v-ljerr-msft,
thanks for your reply! It's already pretty close to what I need, however, there is still a problem. I want to display a matrix with columns date, a hierarchy of User and Tickets as rows and the sum of hours as a value.
I need the measure to filter for users. That way, I want to find all of the users that have spent more than X hours (I'll define X in the filter) a day on all selected tickets (summed up). Your measure gives me just that, however, with the measure as a filter I can't see all of the selected tickets the user has worked on in my selected time period. Instead, only the tickets that the user has worked on on a day with a total of more than X hours are displayed. Do you have any idea how I can see all of the tickets here?

Edit: In addition to that, I can't see users with a total of more than X hours a day if their tickets have less than X hours each. I can see the missing Users if I remove the hierarchy User - Tickets and only display Users in the rows. But I really need the drill down to tickets.

Microsoft

## Re: Calculting the Maximum of a calculated sum depending on filtered data

Hi @Elli91,

Could you try the formula below to see if it works in your scenario?

```Measure =
CALCULATE (
MAXX (
SUMMARIZE (
Abfrage1;
Abfrage1[User];
Abfrage1[Date];
"TotalHours"; SUM ( Abfrage1[Hours] )
);
[TotalHours]
);
ALL ( Abfrage1[Ticket_Id] )
)
```

Regards

Frequent Visitor

## Re: Calculting the Maximum of a calculated sum depending on filtered data

@v-ljerr-msft
Thank you again! At first I thought it worked perfectly, but after checking some more Users I discovered that the total amount of hours is correct (it wasn't before). However, when I drill down, it still doesn't show all of the tickets and the hours don't add up to the user's total amount of hours anymore.

Microsoft

## Re: Calculting the Maximum of a calculated sum depending on filtered data

Hi @Elli91,

Could you post some screenshots with explanation on the issue? It's better to share a sample pbix file which can reproduce the issue, so that I can further assist on it. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Regards

Announcements

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)