topic Re: ABC statics filter in DAX Commands and Tips

ABC statics filter

Julia997 — Tue, 07 Feb 2023 13:22:41 GMT

I have try to create ABC analysis, I have done the first cumulated but after that I found difficult.

My dataset is like this https://1drv.ms/x/s!AvMy-Bzzx3mqgwmKSZm6wr_W4VXr?e=Jwwbji&nav=MTVfezAwMDAwMDAwLTAwMDEtMDAwMC0wMDAwLT...

I need to diversify for each year (statical filter), how can I add?

RANK = RANKX(ALLSELECTED('TABLE'[NAME]),[FATT],,DESC,Dense)

M_Running total =

VAR _currentrank = [RANK]

VAR _t1 =

CALCULATETABLE (

SUMMARIZE ( 'TABLE', 'TABLE'[NAME] ),

ALLSELECTED ( 'TABLE' )

)

VAR _t2 =

ADDCOLUMNS (

_t1,

"@Total", CALCULATE ( SUM ( 'TABLE'[FATT] ) ),

"@Rank", [RANK]

)

RETURN

IF (

ISINSCOPE ( 'TABLE'[NAME] ),

SUMX ( FILTER ( _t2, [@Rank] <= _currentrank ), [@Total] )

)

EDIT: how can I calculated the cumulated? I have try Measure= CALCULATE(SUM[Column]), ALL(Table)) but it doesn't work

Re: ABC statics filter

MasterSonic — Tue, 07 Feb 2023 12:53:38 GMT

this perhaps

RANK = RANKX(FILTER(ALL('TABLE'), YEAR('TABLE'[DATE]) = SELECTEDVALUE(YEAR('TABLE'[DATE]))),[FATT],,DESC,Dense)

M_Running total = SUMX( FILTER( SUMMARIZE( 'TABLE', 'TABLE'[NAME], "Year", YEAR('TABLE'[DATE]) ), (YEAR('TABLE'[DATE]) = SELECTEDVALUE(YEAR('TABLE'[DATE]))) && (RANKX(FILTER(ALL('TABLE'), YEAR('TABLE'[DATE]) = SELECTEDVALUE(YEAR('TABLE'[DATE]))),[FATT],,DESC,Dense) <= [RANK]) ), SUM('TABLE'[FATT]) )

Re: ABC statics filter

Julia997 — Tue, 07 Feb 2023 13:06:17 GMT

My year in this case is a text, not a date. How can I do? The second DAX doesn't work