Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Add count to Dynamic ABC analysis

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?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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
    )
)

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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
    )
)

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.