ABC XYZ dynamic analysis - trouble getting both an ABC and XYZ result

[Anonymous]

I cant figure out if this is even possible.

I have used mainly this resource 

https://www.daxpatterns.com/abc-classification/

as a framework for this piece

For those who dont know, XYZ and ABC are concepts in inventory management

XYZ - regularity over time (standard dev and average required)

ABC - where each product is allocated a proportion of all value in relation to all products total value

For both ABC and XYZ the letter pair each falls into is based on thresholds

Thus a product in AX is high value, and regularly ordered

Conversely CZ is irregular and low value

The problem:

Firstly this needs to be dynamic, based on the view of the report the user is requesting, so follows filters etc

- i believe this makes calculated columns a no go?

in a static calc you can simply calc each product, generate two columns, one for ABC, one for XYZ 

so

product1 - A -X

product2 - A - X

...

product1000 - C - Z

However with this being dynamic i belive this is impossible (calced columns in DAX will not recalc wiuth filter changes)

I have the DAX measures for creating both ABC and XYZ separately.

this allows me for any time frame to allocate part IDs into XYZ and ABC

this is done using the info on the link already posted: 

https://www.daxpatterns.com/abc-classification/

the main feature of this is to produce a number which falls within thresholds in another table (below)

ABC_Threshold	Lower	Upper
A	0	0.8
B	0.8	0.95
C	0.95	1

VAR ProductsInClass =
    FILTER (
        CROSSJOIN (
            CumulatedPctByProduct,
            'ABC Classes'
        ),
        AND (
            [@CumulatedPct] > 'ABC Classes'[Lower Boundary],
            [@CumulatedPct] <= 'ABC Classes'[Upper Boundary]

This allows me to use the ABC_Threshold column as a column in a powerbi table and drop the measure into the 'values'

BUT:

One of the end user requirements is to have a matrix like below

Product count	A	B	C
X	5	20	30
Y	10	40	120
Z	60	100	500

however as my measures work the way they do i cannot figure out how to do this, but i also cannot figure out another way of doing the measures to allow this.

Any ideas?

[Paola1]

Hi,
I can ask you to share the file with the solution?
I have the same problem and I can't get out of it.

Thank you very much.

[v-angzheng-msft]

Thank you for sharing😀

[Anonymous · Accepted solution]

ive successfully solved this, writing it out in a post here gave me an idea nad it worked

I moved the calc for the XYZ into the ABC measure and then included this in the crossjoin 

[Carlosrubick]

Hi al4xms , greetings from Peru.

I am having nightmares with the same problem, could you please share your solution a little more detailed

or drop the doc.

Thanks

[GisWill90]

I need help with same. can you drop me the solution file

[ccluzel]

@Anonymous can you share how you did it, I am struggling with that as well

Thanks

[FabienP]

Dear al4xms,

Can you share in detail your solution, i'm also struggling on this topic.

Thank you in advance