Need help on PowerBI - How to Calculate Greater than value and bring %

[arun2001mjl]

Hello Team,

I have a table with 2 Columns Ticket number & Category.

By Category, if Ticket is more than 5, then i need to sum those tickets and divide by total number of tickets which will give %. I need to do this in PowerBI using DAX. Can someone help me.

Example: Below is the Table with 2 Columns:

Ticket Number	Category
Ticket01	CI1
Ticket02	CI1
Ticket03	CI1
Ticket04	CI1
Ticket05	CI1
Ticket06	CI1
Ticket07	CI1
Ticket08	CI1
Ticket09	CI1
Ticket10	CI1
Ticket11	CI1
Ticket12	CI1
Ticket13	CI1
Ticket14	CI1
Ticket15	CI1
Ticket16	CI6
Ticket17	CI6
Ticket18	CI6
Ticket19	CI6
Ticket20	CI6
Ticket21	CI6
Ticket22	CI6
Ticket23	CI6
Ticket24	CI6
Ticket25	CI6
Ticket26	CI6
Ticket27	CI6
Ticket28	CI6
Ticket29	CI6
Ticket30	CI0
Ticket31	CI0
Ticket32	CI0
Ticket33	CI0
Ticket34	CI0
Ticket35	CI0
Ticket36	CI0
Ticket37	CI7
Ticket38	CI7
Ticket39	CI7
Ticket40	CI0
Ticket41	CI0
Ticket42	CI0
Ticket43	CI0
Ticket44	CI0
Ticket45	CI0
Ticket46	CI0
Ticket47	CI0
Ticket48	CI0
Ticket49	CI0
Ticket50	CI0
Ticket51	CI0
Ticket52	CI0
Ticket53	CI0
Ticket54	CI0
Ticket55	CI0
Ticket56	CI0
Ticket57	CI0
Ticket58	CI0
Ticket59	CI0
Ticket60	CI0
Ticket61	CI0
Ticket62	CI0
Ticket63	CI0
Ticket64	CI4
Ticket65	CI4
Ticket66	CI4
Ticket67	CI4
Ticket68	CI4
Ticket69	CI4
Ticket70	CI4
Ticket71	CI4
Ticket72	CI4
Ticket73	CI4
Ticket74	CI4
Ticket75	CI4
Ticket76	CI4
Ticket77	CI4
Ticket78	CI8
Ticket79	CI9
Ticket80	CI0
Ticket81	CI0
Ticket82	CI0
Ticket83	CI0
Ticket84	CI0
Ticket85	CI0
Ticket86	CI0
Ticket87	CI0
Ticket88	CI0
Ticket89	CI0
Ticket90	CI0
Ticket91	CI0
Ticket92	CI0
Ticket93	CI0
Ticket94	CI0
Ticket95	CI0
Ticket96	CI0
Ticket97	CI0
Ticket98	CI0
Ticket99	CI0
Ticket100	CI0
Ticket101	CI0
Ticket102	CI0
Ticket103	CI0
Ticket104	CI0
Ticket105	CI0
Ticket106	CI0
Ticket107	CI0
Ticket108	CI0
Ticket109	CI0
Ticket110	CI10
Ticket111	CI11
Ticket112	CI11
Ticket113	CI11
Ticket114	CI11
Ticket115	CI11
Ticket116	CI16
Ticket117	CI16
Ticket118	CI16
Ticket119	CI19
Ticket120	CI19
Ticket121	CI21
Ticket122	CI22
Ticket123	CI22
Ticket124	CI24
Ticket125	CI24
Ticket126	CI26
Ticket127	CI26
Ticket128	CI26
Ticket129	CI26
Ticket130	CI26
Ticket131	CI26
Ticket132	CI26
Ticket133	CI26
Ticket134	CI26
Ticket135	CI26
Ticket136	CI26
Ticket137	CI26
Ticket138	CI26
Ticket139	CI26
Ticket140	CI26
Ticket141	CI26
Ticket142	CI26
Ticket143	CI26
Ticket144	CI26
Ticket145	CI26
Ticket146	CI46
Ticket147	CI47
Ticket148	CI47
Ticket149	CI47
Ticket150	CI47
Ticket151	CI47
Ticket152	CI47
Ticket153	CI53
Ticket154	CI54

Example: Below is the Pivot and colored "Greater than 5 Tickets".

We have 130 tickets which are greater than 5 Divided by Total Tickets which is 154 which brings 84%.

Row LabelsCount of Ticket NumberGrand Total154

CI0	61		84%
CI26	20
CI1	15
CI6	14
CI4	14
CI47	6
CI11	5
CI7	3
CI16	3
CI22	2
CI24	2
CI19	2
CI21	1
CI8	1
CI46	1
CI53	1
CI9	1
CI54	1
CI10	1
(blank)

Can we create this in PowerBI using DAX?

[ppm1 · Accepted solution]

Please try this measure expression. Replace Tickets with your actual table name.

Pct Over 5 =
VAR tSummary =
    ADDCOLUMNS (
        VALUES ( Tickets[Category] ),
        "cCount", CALCULATE ( COUNTROWS ( Tickets ) )
    )
RETURN
    DIVIDE (
        SUMX ( FILTER ( tSummary, [cCount] > 5 ), [cCount] ),
        SUMX ( tSummary, [cCount] )
    )

Pat

[arun2001mjl · Accepted solution]

Solution Worked. Thank you very much.

[ppm1 · Accepted solution]

Please try this measure expression. Replace Tickets with your actual table name.

Pct Over 5 =
VAR tSummary =
    ADDCOLUMNS (
        VALUES ( Tickets[Category] ),
        "cCount", CALCULATE ( COUNTROWS ( Tickets ) )
    )
RETURN
    DIVIDE (
        SUMX ( FILTER ( tSummary, [cCount] > 5 ), [cCount] ),
        SUMX ( tSummary, [cCount] )
    )

Pat