PARETO ANALYSIS - Cumulatiive % problem.

[vijay273162]

HI,

I need to do pareto analysis (80/20) based table matrix.

Below is a simple example, where i can't able to display "GM" in table matrix.

I need to apply IF logic, which says, which gives me next row. "GM" in table matrix.

[vijay273162]

Thanks for reply. I have checked these articles already.

My requirement is to show column "% of TOTAL SALES" with row Total at bottom.

So, if I apply IF logic, it return only 2 rows, which gives row Total as "61%" .

So, I need to get row total for "column - % of total sales"  =80% or the next row even if exceeds 80%.

[v-zhangti]

Hi, @vijay273162 

I don't know if my understanding is correct. You can check the following methods.

Column:

Cumulative % of Total Sales =
CALCULATE (
    SUM ( 'Table'[% of Total Sales] ),
    FILTER (
        'Table',
        [Rank Based on Sales] <= EARLIER ( 'Table'[Rank Based on Sales] )
    )
)

IF 80% = 
IF (
    [Cumulative % of Total Sales] < 0.8,
    1,
    IF (
        [Cumulative % of Total Sales] >= 0.8
            && [Rank Based on Sales]
                = CALCULATE (
                    MIN ( 'Table'[Rank Based on Sales] ),
                    FILTER ( 'Table', [Cumulative % of Total Sales] >= 0.8 )
                ),
        1,
        0
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vijay273162]

I have direct query, so need to calculate measure for all Columns except company and Sales.

can you create [IF 80%] Column as MEASURE. using 

[Cumulative % of Total Sales] ----------- as MEAURE

[Rank Based on Sales] ------------------- as MEASURE

Here are the MEASURES I created for above.

Rank = RANKX(ALLSELECTED('Table (2)'[Company]),[Total sales],,DESC)

**bleep**. % of Total =

IF(HASONEVALUE('Table'[Company]),

CALCULATE([% of Total],TOPN([Rank],ALL('Table (2)'[Company]),[% of Total]))

  )

Please let me know, how to apply this 2 measures in [IF 80%] measure to get table like below with company "GM"included.

[v-zhangti · Accepted solution]

Hi, @vijay273162 

This time, according to your data, except for the company and sales, the rest are Measure.

Cum % of Total = 
Var N1 = SUMMARIZE(ALL('Table'),'Table'[Sales],'Table'[Company])
Var N2 = GENERATE(N1,ROW("Cum % of Total",[% of Total]))
Var N3 = [% of Total]
Var _filter=FILTER(N2,[Cum % of Total]>=N3)
return SUMX(_filter,[Cum % of Total])

IF 80% = 
IF ([Cum % of Total]< 0.8,
    1,
    IF (
        [Cum % of Total] >= 0.8
            && SELECTEDVALUE('Table'[Sales])
                = CALCULATE (
                    Max ( 'Table'[Sales] ),
                    FILTER ( ALL('Table'), [Cum % of Total] >= 0.8 )
                ),
        1,
        0))

Please check whether the results meet your expectations and communicate in time if you have any questions.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@vijay273162 , why cummulative is more 100% ?

Also check

Top 80/20 , percent /percentile
https://blog.enterprisedna.co/implementing-80-20-logic-in-your-power-bi-analysis/
https://forum.enterprisedna.co/t/testing-the-pareto-principle-80-20-rule-in-power-bi-w-dax/459
https://finance-bi.com/power-bi-pareto-analysis/