DCount items by conditional cumulative value

[EvgenyZhutov]

Hi All, 

I have 5 DAX-formulas for calculating paretto (ABC analysis)

1) Total Sales = sum('Table1'[Sales])

2) Total Sales for all products = SUMX(all('Table1'[Product]);[Total Sales])

3) Rank = if(HASONEVALUE('Table1'[Product]);RANKX(ALL('Table1'[Product]);[Total Sales]);BLANK())

4) Cumulativa total = if(HASONEVALUE('Table1'[Product]);SUMX(TOPN([Rank];all('Table1'[Product]);[Total Sales];DESC);[Total Sales]);BLANK())

5)  Cumulative percentage = DIVIDE([Cumulativa total];[Total Sales for all products];0)*100

It's solution working fine and correctly calculation.

How i can calculate DCount products for A (<0,8), B (>0,8<0,95, C (>0,95) in 3 measure.

E.g. A = 50 products; B=40 products; C= 13 products

[lc_finance]

Hi @EvgenyZhutov ,

can you try the following measures:

DCount A = SUMX(
   VALUES('Table1'[Product]), 
   IF([Cumulative percentage]<0.8,1,0) 
)

DCount B = SUMX(
   VALUES('Table1'[Product]), 
   IF(
     AND([Cumulative percentage]>=0.8,[Cumulative percentage]<0.95 )
   ,1,0) 
)

DCount A = SUMX(
   VALUES('Table1'[Product]), 
   IF([Cumulative percentage]>0.95,1,0) 
)

Let me know if they work for you.

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com