Unique Count of Invoice and Total Product Spend based on Vendor from Top 80% Spend

[joycewoo]

Dear Community,

I search this forum with many different keywords but still cannot get to solution yet, totally no idea how to tackle this.

The complex part is that the value should be responsive to all present filters, so calculated column cannot achieve the result, believe it has to be dynamic measures. 

Sample data like this:

Vendor	InvoiceId	Department	ProductId	Spend Amount
Vendor A	INV001	Dept1	Product1	10
Vendor A	INV001	Dept1	Product2	20
Vendor A	INV001	Dept3	Product3	50
Vendor A	INV001	Dept4	Product1	40
Vendor A	INV002	Dept2	Product1	25
Vendor A	INV002	Dept3	Product3	80
Vendor A	INV002	Dept3	Product1	100
Vendor B	INV003	Dept1	Product1	90
Vendor B	INV003	Dept1	Product2	80
Vendor B	INV003	Dept3	Product3	70
Vendor B	INV003	Dept4	Product1	50
Vendor B	INV004	Dept2	Product1	15
Vendor B	INV004	Dept1	Product2	25
Vendor B	INV004	Dept3	Product3	35
Vendor B	INV004	Dept3	Product1	45
Vendor C	INV005	Dept1	Product1	90
Vendor C	INV005	Dept1	Product1	36
Vendor D	INV006	Dept1	Product1	90
Vendor D	INV006	Dept1	Product1	150
Vendor D	INV006	Dept1	Product1	15
Vendor D	INV007	Dept2	Product2	35
Vendor D	INV007	Dept3	Product3	20

Desired result:

Visual A is total spend amount by vendor (shows that Vendor B & Vendor A are contributed to Top 80% of spend amount).

Visual B (spend/inv count by product), C1 and C2 (inv count based on amount range) are calculated based on Top 80% Spend Vendors.

I only managed to achieve visual A, but unable get the result for visual B, C1 and C2. 

Any help would be very much appreciated.

Thanks in advance. 

[joycewoo]

Try on this dax but didn't get the correct result. Can anyone helps? 

Top Invoice # =

VAR SpendByParentSupplier =

    ADDCOLUMNS (

        ALLSELECTED ( Data[Vendor]),

        "SpendAmount", [Total Spend],

        "Vendor1", Data[Vendor]

    )

VAR AllSpend =

    CALCULATE (

        [Total Spend],

        ALLSELECTED ( Data[Vendor] )

    )

VAR CumulatedPercent =

    ADDCOLUMNS (

        SpendByParentSupplier,

        "Cumulative%",

        VAR CurrentSpendAmount = [SpendAmount]

        VAR CumulatedSpend =

            FILTER (

                SpendByParentSupplier,

                [SpendAmount] >= CurrentSpendAmount

            )

        VAR CumulatedSpendAmount =

            SUMX (

                CumulatedSpend,

                [SpendAmount]

            )

        RETURN

            DIVIDE (

                CumulatedSpendAmount,

                AllSpend

            )

    )

VAR FilteredTable =

    filter( CumulatedPercent, [Cumulative%] <= 0.8)

VAR SpendbyInv =

    ADDCOLUMNS(

        ALLSELECTED ( Data[Vendor], Data[ProductId],Data[InvoiceId]),

        "SpendAmount1", [Total Spend],

        "Vendor1", Data[Vendor]

    )

VAR FilteredTable2 =

    FILTER(

        CROSSJOIN(

            SpendbyInv,

            'Range'),

            [SpendAmount1] >='Range'[Min Value] &&

                [SpendAmount1] < 'Range'[Max Value]

    )

Var JoinedTable =

        NATURALINNERJOIN(

            FilteredTable,

            FilteredTable2)

RETURN

    Calculate([Invoice Count],  KEEPFILTERS( JoinedTable ))

[CNENFRNL]

Your question is an variant of common Pareto analysis; but I'm not surprised that you can't solve it after a deep look into it. Its intricacy is way more than it appears. It's way beyond most users.

[joycewoo]

I gather Dynamic ABC Classification from this post might be helpful but I just don't know how to fit it to my case. 
ABC classification – DAX Patterns

[ddpl]

@joycewoo ,

Can you enlighten us how you got Visual B ???, especially Spent Amount(Top 80% Spend Vendors) more explicitly.

[joycewoo]

Hi, thanks for replying to my question. 

Vendors are ranked/sorted based on total spend amount, from largest total amount ranked as first to smallest. Based on the sorted vendor then calculate the cumulative percentage. Lastly, vendors with cumulative percentage from 0% up to 80% are categorised as Top 80% spend vendors. (this is what you can see from visual A).

From the sample data given above, only vendor A and B are within cumulative 80% (vendor D & C already exceeded 80% so out of scope). 

So, visual B is the total amount and invoice count by product for Top 80% spend vendors, which is vendor A and B only. This two vendors contributed 3 products (Product1, Product2 and Product3). 

Below is example how to get the total amount ($375) and invoice count (4 invoices) for Product1. 

I hope this helps.