cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Elli91 Frequent Visitor
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 v-ljerr-msft
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? Smiley Happy

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

 

Regards

View solution in original post

5 REPLIES 5
Microsoft v-ljerr-msft
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. Smiley Happy

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

 

Regards

Elli91 Frequent Visitor
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 v-ljerr-msft
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? Smiley Happy

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

 

Regards

View solution in original post

Elli91 Frequent Visitor
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 v-ljerr-msft
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. Smiley Happy

 

Regards

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

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

Difinity Conference

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

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)