Forum Discussion

BieBel's avatar
BieBel
Helper I
2 years ago
Solved

Power Query calculate percentage for specific year

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?

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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)

  • BieBel's avatar
    BieBel
    Helper I
    2 years ago

    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!

  • BieBel's avatar
    BieBel
    Helper I
    2 years ago

    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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.