cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ThePoulsen
New Member

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

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 III
Super User III

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/

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors