ABC Analysis

Question

Hi everyone, I am trying to integrate the ABC Classfication in Power BI, however, I am struggling with how to integrate filters. This is the formula I have used:&nbsp;Amount ABC =var Outer =ADDCOLUMNS(&nbsp;&nbsp;&nbsp; VALUES(Jobs_Details[CLIENT_CODE]),&nbsp;&nbsp;&nbsp;&nbsp;"OuterValue", [Gross Profit])var Inner&nbsp; =ADDCOLUMNS(&nbsp;&nbsp;&nbsp; ALL(Jobs_Details[CLIENT_CODE]),&nbsp;&nbsp;&nbsp; "InnerValue", [Gross Profit])returnCALCULATE(&nbsp;&nbsp;&nbsp; [Gross Profit],&nbsp;&nbsp;&nbsp; VALUES(Jobs_Details[CLIENT_CODE]),&nbsp;&nbsp;&nbsp; FILTER(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CALCULATETABLE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ADDCOLUMNS (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Outer,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "CumulatedPercentage", DIVIDE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUMX (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER (&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Inner,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [InnerValue] &gt;= [OuterValue]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [InnerValue]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [Gross Profit],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VALUES (Jobs_Details[CLIENT_CODE])&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ALL(Jobs_Details)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , [CumulatedPercentage] &gt; CALCULATE( MIN('ABC Class'[LowerBoundary]) )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp;&amp; [CumulatedPercentage] &lt;= CALCULATE( MAX('ABC Class'[UpperBoundary]) )&nbsp;&nbsp;&nbsp; ))The issue is that when I select another filter such as a Rate Table Number so I could see all Class A client that uses a specifc pricing strategy, it will grouped all client under Class A while originally without that filter selected, the client is a Class C. Moreover, when I try to look for a specific client using the search function, I can see the client is duplicate with only information attached to one Class however, it is the wrong class too. Any idea how to fix this so it is dynamic no matter which filter is used? Thank you.

amitchandak · Answer

Refer if this can helphttps://community.powerbi.com/t5/Desktop/ABC-Analysis/td-p/309108https://www.daxpatterns.com/abc-classification/https://exceed.hr/en/blog/dynamic-abc-analysis-in-dax-variables

v-yuta-msft · Answer

corange&nbsp;,
&nbsp;

The issue is that when I select another filter such as a Rate Table Number so I could see all Class A client that uses a specifc pricing strategy, it will grouped all client under Class A while originally without that filter selected, the client is a Class C. Moreover, when I try to look for a specific client using the search function, I can see the client is duplicate with only information attached to one Class however, it is the wrong class too. Any idea how to fix this so it is dynamic no matter which filter is used? Thank you.

I'm quite confused on your description. could you share more details about this issue? (e.g.: relationship between Rate Table and Fact table, which column are you trying to divide and what does the search expression look like)
&nbsp;
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

corange · Answer

Hi,&nbsp;&nbsp;THanks for your reply.&nbsp;&nbsp;Can I share my document with you privately somehow?&nbsp;

v-yuta-msft · Answer

corange&nbsp;,
&nbsp;
Please do not sent the orginal file. You can make a simple sample file, upload the file to onedrive and share the link here.
&nbsp;
Regards,
Jimmy Tao

Forum Discussion

ABC Analysis

4 Replies

Related content

|| ABC Analysis ||

ABC Analysis

ABC analysis

Cohort analysis

How to Create a Chart Visual for ABC Analysis