Add count to Dynamic ABC analysis

[Anonymous]

I have followed this guide https://www.daxpatterns.com/abc-classification-dynamic/ in order to create a dynamic ABC model to illustrate our spend by supplier (Slightly modified code below)

I can now line up the ABC class, and group by supplier region (from supplier dimension table) and get the spend amount no problem.

I can however not get a distinct count of the suppliers within each ABC class, it appears I need some sort of relation as the result is the same for each category.

SupplierABC = 
var MinLowerBoundary = MIN ( ABC_Classification[Lower])
var MaxUpperBoundary = Max ( ABC_Classification[Upper])
RETURN
CALCULATE (
    [Spend];
    VALUES ( Dim_Supplier[Supplier_Key] );
    FILTER (
        CALCULATETABLE (
            ADDCOLUMNS (
                ADDCOLUMNS (
                    VALUES ( Dim_Supplier[Supplier_Key] );
                    "OuterValue"; [Spend]
                );
                "CumulatedSalesPercentage"; DIVIDE (
                    SUMX (
                        FILTER (
                            ADDCOLUMNS (
                                VALUES ( Dim_Supplier[Supplier_Key] );
                                "InnerValue"; [Spend]
                            );
                            [InnerValue] >= [OuterValue]
                        );
                        [InnerValue]
                    );
                    CALCULATE (
                        [Spend];
                        VALUES ( Dim_Supplier[Supplier_Key] )
                    )
                )
            );
            ALL ( 'Dim_Supplier' )
        );
        [CumulatedSalesPercentage] > MinLowerBoundary
            && [CumulatedSalesPercentage] <= MaxUpperBoundary
    )
)

Any ideas?

[Ashish_Mathur]

Hi,

Does this work?

SupplierABC = 
var MinLowerBoundary = MIN ( ABC_Classification[Lower])
var MaxUpperBoundary = Max ( ABC_Classification[Upper])
RETURN
COUNTROWS (
    VALUES ( Dim_Supplier[Supplier_Key] );
    FILTER (
        CALCULATETABLE (
            ADDCOLUMNS (
                ADDCOLUMNS (
                    VALUES ( Dim_Supplier[Supplier_Key] );
                    "OuterValue"; [Spend]
                );
                "CumulatedSalesPercentage"; DIVIDE (
                    SUMX (
                        FILTER (
                            ADDCOLUMNS (
                                VALUES ( Dim_Supplier[Supplier_Key] );
                                "InnerValue"; [Spend]
                            );
                            [InnerValue] >= [OuterValue]
                        );
                        [InnerValue]
                    );
                    CALCULATE (
                        [Spend];
                        VALUES ( Dim_Supplier[Supplier_Key] )
                    )
                )
            );
            ALL ( 'Dim_Supplier' )
        );
        [CumulatedSalesPercentage] > MinLowerBoundary
            && [CumulatedSalesPercentage] <= MaxUpperBoundary))

If not, then share a dataset and show the expected result.

[Anonymous · Accepted solution]

It didn't quite work

This solution however did.

SupplierABC = 
var MinLowerBoundary = MIN ( ABC_Classification[Lower])
var MaxUpperBoundary = Max ( ABC_Classification[Upper])
RETURN
CALCULATE (
    COUNTDISTINCT(Dim_Supplier[Supplier_Key]);
    VALUES ( Dim_Supplier[Supplier_Key] );
    FILTER (
        CALCULATETABLE (
            ADDCOLUMNS (
                ADDCOLUMNS (
                    VALUES ( Dim_Supplier[Supplier_Key] );
                    "OuterValue"; [Spend]
                );
                "CumulatedSalesPercentage"; DIVIDE (
                    SUMX (
                        FILTER (
                            ADDCOLUMNS (
                                VALUES ( Dim_Supplier[Supplier_Key] );
                                "InnerValue"; [Spend]
                            );
                            [InnerValue] >= [OuterValue]
                        );
                        [InnerValue]
                    );
                    CALCULATE (
                        [Spend];
                        VALUES ( Dim_Supplier[Supplier_Key] )
                    )
                )
            );
            ALL ( 'Dim_Supplier' )
        );
        [CumulatedSalesPercentage] > MinLowerBoundary
            && [CumulatedSalesPercentage] <= MaxUpperBoundary
    )
)