Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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
Solution Worked. Thank you very much.
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
User | Count |
---|---|
101 | |
90 | |
80 | |
71 | |
70 |
User | Count |
---|---|
114 | |
98 | |
97 | |
73 | |
72 |