ABC Analysis to show class% by store count

[wenchi]

Hi, 

I have a need to do dynamic ABC class analysis and would like to show the ratio% among all pre-defined class by store count. Down below shows the example of what I need to accomplish. Basically, the user can pick any week and any brand, then we need to find the price gap for selected brand to the lowest price (any brand) in each store. Based on the price gap, we then assign the class by pre-defined rules. At the end, we want to show the ratio of each class by store count. 

We do have a store dimension table. I think I can use summarize funtion to get the lowest price for each store to start. Then I can use addcoumn function to add the selected brand price and price gap and class. I can calculate the ratio for each class and use bar chart to display the result. This is not the best solution since I have to do the same calculation for each class. The ideal is to do one dax and to be able to display the ratio.

By the way, I am using live connection to Azure AS model, so not much I can change to tables\columns in PBI file.

Any helps is very appreciated.

Thanks,

Wenchi Wang

[Anonymous]

Hi  @wenchi 

Has your problem been solved ? If it has been solved, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Whitewater100]

Hi:

Do you have a configuration table, something like:

Are you counting the number of stores or items that fall into a certain class?

On the example grid above it shows 17% across all the stores and ABC classes. Is this the way it would work?

If all the main data is in the one table above you can try:

First a measure like Avg Price Gap = AVERAGE(Table[Price Gap])

Then another measure:

Cust CT by Segment =

COUNTROWS(

FILTER(Stores,

AND(

[Avg Price Gap] > MIN( Segments[MinGap]),

[Avg Price Gap] <= MAX(Segments[MaxGap])

)))

Example Image

[wenchi]

We currently don't have a config table, but we do have a business rule to apply to assign the class for each store based on price gap. Back to my example, Yes, I am counting the store for each class and divided by total number of stores to get the ratio. For example, I have 3 stores fall under "Parity", so it represents of 50% (3/6) for parity based on store count. 

The real chellenge is I can't create one separate measure for price gap since it's not stored in any physical table. I have to calculate the price gap and compute class ratio one at a time, which is very inefficient approach.

Wenchi

[Whitewater100]

Hi wenchi:

I'm not too sure how to help further without seeing an example data model with all the information (table(s) and columns you are referring to.  I could try to guess but probablywould be off. Having a disconnected table to run logic thru is how I usually handle these types of challenges. Thanks.

[wenchi]

 can i email you the sample file? I can't find a way to attach file through the post.

[Whitewater100]

OK. sounds good. Can you also provide what an answer would look like?

skelleybill@gmail.com is my email. Thanks..