Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Elli91
Regular 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

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
v-ljerr-msft
Employee
Employee

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

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.

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

@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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.