powerbi分组

 

 

group	name	type	date	qty
A	grant	male	2025/1/1	1
A	edmond	male	2025/1/2	2
A	jush	female	2025/1/3	3
B	jella	female	2025/1/4	4
B	Hugs	male	2025/1/5	5
C	Tagsz	male	2025/1/6	6

　　

 

 

1. 以group和type分组, 按date倒叙, 每组取第一个, 形成一个新的表, 新的表和这个表列一样多.

NewTable = 
FILTER(
    'base',
    VAR CurrentGroup = 'base'[group]
    VAR CurrentType = 'base'[type]
    VAR CurrentDate = 'base'[date]
    RETURN
    CurrentDate = 
        MAXX(
            FILTER(
                'base',
                'base'[group] = CurrentGroup &&
                'base'[type] = CurrentType
            ),
            'base'[date]
        )
)

　　

使用 SELECTCOLUMNS 重命名列

NewTable = 
VAR GroupedData = 
    GENERATE(
        SELECTCOLUMNS(
            SUMMARIZE(
                'base',
                'base'[group],
                'base'[type]
            ),
            "GroupValue", 'base'[group],
            "TypeValue", 'base'[type]
        ),
        TOPN(
            1,
            FILTER(
                'base',
                'base'[group] = [GroupValue] &&
                'base'[type] = [TypeValue]
            ),
            'base'[date], DESC
        )
    )
RETURN
    GroupedData

　　

 

2. 以group, type分组, 计算qty数量占该组的比例,这个比例数据作为一个新增列 rate附加到这个表

rate = 
VAR CurrentGroup = 'base'[group]
VAR CurrentType = 'base'[type]
VAR CurrentRowQty = 'base'[qty]  -- 当前行的 qty
VAR GroupTypeTotalQty = 
    CALCULATE(
        SUM('base'[qty]),
        FILTER(
            ALL('base'),
            'base'[group] = CurrentGroup && 'base'[type] = CurrentType
        )
    )
RETURN
    DIVIDE(CurrentRowQty, GroupTypeTotalQty, 0)