Forum Discussion

vijay273162's avatar
vijay273162
Icon for Helper III rankHelper III
4 years ago
Solved

PARETO ANALYSIS - Cumulatiive % problem.

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.

 

 

 

Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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.

5 Replies

  • vijay273162's avatar
    vijay273162
    Icon for Helper III rankHelper III
    4 years ago

     

    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's avatar
      v-zhangti
      Icon for Community Support rankCommunity Support
      4 years ago

      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's avatar
        vijay273162
        Icon for Helper III rankHelper III
        4 years ago

        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.