Calculating Pareto Table for long-type data in Power BI

Question

I have a dataset that looks like so:

ID SALE	PRODUCT	REGION
SE_056	AAA	NORTH
XT-558	XXX	NORTH
8547Y	CCC	NORTH
TY856	XXX	NORTH
D-895	BBB	SOUTH
ER5H	CCC	SOUTH
5F6F-GD	CCC	SOUTH
65-FFD	TTT	SOUTH
56-YU	XXX	SOUTH

I would like to create a table that will show me the subtotal (COUNT) for each product as well as their indivudal percentage and cumulative percentage like so:

PRODUCT	subtotal	Percentage	Cum Percentage
CCC	3	0,33333333	0,333333333
XXX	3	0,33333333	0,666666667
AAA	1	0,11111111	0,777777778
BBB	1	0,11111111	0,888888889
TTT	1	0,11111111	1

I want this table to be sort by highest subtotal to lowest subtotal and that in that matter the cumulative percentage be as well calculated, PBI is not easy. I cannot find a way to get my table to be sort by highest subtotal and have the cum % calculated in that same order.

Answer 1

You can do this in a report visualisation with a few measures, to calculate number of sales, percentage of sales, product rank by sales (then product name), and cumulative sales percentage:

# Sales:

# Sales = COUNT ( Sales[ID SALE] )

% Sales:

% Sales = 
    DIVIDE (
        [# Sales],
        CALCULATE ( 
            [# Sales],
            REMOVEFILTERS ( Sales[PRODUCT] )
        )
    )

% Sales (Cumulative):

% Sales (Cumulative) = 
VAR CurrentRank = 
    IF ( 
        ISINSCOPE ( Sales[PRODUCT] ),
        [Rank by Sales then Product],
        DISTINCTCOUNT ( Sales[PRODUCT] )
    )
RETURN
    SUMX ( 
        ALL ( Sales[PRODUCT] ),
        IF ( 
            [Rank by Sales then Product] <= CurrentRank,
            [% Sales],
            BLANK()
        )
    )

Rank by Product:

Rank by Product = 
    RANKX ( 
        ALL ( Sales[PRODUCT] ),
        FIRSTNONBLANK ( Sales[PRODUCT], 1 ),,
        ASC,
        Dense
    )

Rank by Sales then Product:

Rank by Sales then Product = 
    RANKX (
        ALL ( Sales[PRODUCT] ),
        [# Sales]  + ( 1 / [Rank by Product] ),,
        DESC,
        Dense
    )

Sample output:

EDIT: You could do the % Sales (Cumulative) measure in on, and remove the 'Rank' measures, but it's significantly less legible:

% Sales (Cumulative) = 
VAR CurrentRank = 
    IF ( 
        ISINSCOPE ( Sales[PRODUCT] ),
        RANKX (
            ALL ( Sales[PRODUCT] ),
            [# Sales]  + 
            DIVIDE ( 
                1,
                CALCULATE (
                    RANKX ( 
                        ALL ( Sales[PRODUCT] ),
                        FIRSTNONBLANK ( Sales[PRODUCT], 1 ),,
                        ASC,
                        Dense
                    )
                )
            ),,
            DESC,
            Dense
        ),
        DISTINCTCOUNT ( Sales[PRODUCT] )
    )
RETURN
    SUMX ( 
        ALL ( Sales[PRODUCT] ),
        IF ( 
            RANKX (
                ALL ( Sales[PRODUCT] ),
                [# Sales]  + 
                DIVIDE ( 
                    1,
                    CALCULATE (
                        RANKX ( 
                            ALL ( Sales[PRODUCT] ),
                            FIRSTNONBLANK ( Sales[PRODUCT], 1 ),,
                            ASC,
                            Dense
                        )
                    )
                ),,
                DESC,
                Dense
            ) <= CurrentRank,
            [% Sales],
            BLANK()
        )
    )