Power Query calculate percentage for specific year

[BieBel]

I have a ticketing system (data table 'output-tickts') and I want to calculate what % of the tickets was created in each year. My calculated % is OK if I I take all data into account (total = 100%) but I would like that, when filtering on one specific year (e.g. 2021), the total for 2021 is also 100% and the % for the different months is only based on the total number of tickets of 2021 is calculated. Same for multiple years selected: calculate % on displayed data, not on whole of data.

I have a measure that is OK for a % 'overall' 

%OfRequests =

 VAR Tickets=

     SUM(‘output-tickets’[IA])

 VAR AllTickets =

     SUMX(

ALL(‘output-tickets’),

       ‘output-tickets’[IA])

RETURN

DIVIDE(Tickets, AllTickets)

How do I adjust this to make the % 'follow' the year filter?

[Anonymous · Accepted solution]

Hi @BieBel 

Do you mean that the total amount you want to account for means the total amount of screening? Let's say for 2021, what do you want to show is output-tickets' [IA] as a percentage of the 2021 total? If so, you can refer to the following measures

%OfRequests =
VAR Tickets =
    SUM ( 'output-tickets'[IA] )
VAR AllTickets =
    SUMX ( ALLSELECTED( 'output-tickets' ), 'output-tickets'[IA] )
RETURN
    DIVIDE ( Tickets, AllTickets )

Best Regards!

Yolo Zhu

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

[BieBel]

I'm not quite where I want to be :-(. I will search the forum a bit further/deeper. I may not have described the expected outcome accurately enough.

Thanks!

[BieBel]

I'm not quite where I want to be :-(. Probably dit not I will search the forum a bit further/deeper. I may not have described the expected outcome accurately enough.

Thanks!

[Anonymous]

Try something like this:

%OfRequestsAdjusted =
VAR SelectedYear =
SELECTEDVALUE('output-tickets'[YearColumn])
VAR TicketsInSelectedYear =
CALCULATE(
SUM('output-tickets'[IA]),
FILTER(ALL('output-tickets'), 'output-tickets'[YearColumn] = SelectedYear)
)

VAR AllTicketsInSelectedYear =
CALCULATE(
SUMX(ALL('output-tickets'), 'output-tickets'[IA]),
FILTER(ALL('output-tickets'), 'output-tickets'[YearColumn] = SelectedYear)
)
RETURN
DIVIDE(TicketsInSelectedYear, AllTicketsInSelectedYear)