Forum Discussion

BieBel's avatar
BieBel
Icon for Helper I rankHelper 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%) b...
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.

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)