Power BI 分组TopN排名表格

wujunmin

发布于 2025-09-03 18:47:25

1440

文章被收录于专栏：wujunminwujunmin

效果如下图所示，展示每个店铺的Top3业绩员工：

这里使用了DAX结合HTML制作表格，表格度量值如下：

HTML.业绩看板.静态 = 
"
<head>
   <style>
        .container {
            max-width: 1000px;
            margin: 0 auto;
            background: white;
            border-radius: 10px;
            box-shadow: 0 0 20px rgba(0, 0, 0, 0.1);
            overflow: hidden;
        }
        
        .department {
            font-weight: bold;
            color: #0d47a1;
            width: 20%; 
        }
        .table-container {
            padding: 20px;
            overflow-x: auto;
        }
        
        table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 20px;
            table-layout: fixed; 
        }
        
        th, td {
            padding: 16px 20px;
            text-align: left;
            border-bottom: 1px solid #e0e0e0;
            vertical-align: middle;
        }
        
        th {
            background-color: #e3f2fd;
            font-weight: bold;
            color: #1565c0;
        }
        
        
        
        .stas-list {
            position: relative;
            overflow: hidden;
            height: 60px;
            width: 100%; 
            display: flex;
            align-items: center;
        }
        .sta-item {
            display: inline-flex;
            align-items: center;
            margin-right: 30px;
            background: #e8f4ff;
            padding: 8px 15px;
            border-radius: 20px;
            box-shadow: 0 2px 5px rgba(0, 0, 0, 0.05);
            flex-shrink: 0; 
        }
        
        .sta-number {
            background: #2196f3;
            color: white;
            width: 26px;
            height: 26px;
            border-radius: 50%;
            display: flex;
            align-items: center;
            justify-content: center;
            margin-right: 10px;
            font-size: 14px;
            font-weight: bold;
            flex-shrink: 0; 
        }
        
        .sta-name {
            font-size: 16px;
            white-space: nowrap;
            overflow: hidden;
            text-overflow: ellipsis;
        }  
  
    </style>
</head> 
<body>
    <div class='container'>
        
        <div class='table-container'>
            <table>
                <thead>
                    <tr>
                        <th class='department'>店铺名称</th>
                        <th>人员业绩排名</th>
                    </tr>
                </thead>
                <tbody>" & CONCATENATEX(VALUES('表'[店铺名称]),
                VAR t =TOPN(3, VALUES(表'[销售员]),[M.销售业绩],DESC)
                RETURN "
                    <tr>
                        <td class='department'>" & [店铺名称] & "</td>
                        <td>
                            <div class='stas-list'>
                                <div class='scroll-container scroll-animation'>" & 
                                CONCATENATEX(t,"
                                    <div class='sta-item'>
                                        <span class='sta-number'>" & ROWNUMBER(t,ORDERBY([M.销售业绩],DESC)) & "</span>
                                        <span class='sta-name'>" & [销售员] & " | " & FORMAT([M.销售业绩],"#,#") & "</span>
                                    </div>" ,,[M.销售业绩],DESC) & "
                                </div>
                            </div>
                        </td>
                    </tr>" ) & "
                </tbody>
            </table>
        </div>
    </div>
</body>"

把度量值放入HTML Content视觉对象即可正常显示。HTML表格在Power BI有很多优势，参考Power BI HTML 表格的四大优势