Pareto Analysis for Top N Accounts

[Anonymous]

Hi All,

I have Top N Parameter, Entier Report want to change based on Top N Parameter Value (Table, Pareto Chart and KPI)

I can control Table By using below Script 

Is Top X selected? = 
IF (
    RANKX (
        ALL ( 'Opportunity'[AccountId] ),
        CALCULATE ( SUM ( 'Opportunity'[Amount] ) ),
        ,
        DESC,
        DENSE
    )
        <= SELECTEDVALUE ( 'Top N Account'[Top N Account Parameter] ),
    "Yes"
)

But I can't control Pareto Chart and KPI Value

Ex: I have given 10 as the parameter value, So Table shows 11 accounts because  two accounts have the same value 

I want to perform a Pareto analysis only for these 11 accounts and KPI want to so 11 

How can i achieve this logic 

Data:

AccountId	Opportunity Id	Amount	CreatedDate	CloseDate	Group Opportunities
0011I000008SIShQAO	0061I000005YRCMQA4	50000	11/8/2017	8/26/2017	Win
0011I000008SIShQAO	0061I000005YRCbQAO	40000	11/8/2017	11/10/2017	Active
0011I000008SIShQAO	0061I000005YRCFQA4	30000	11/8/2017	11/4/2017	Win
0011I000008SISiQAO	0061I000005YRCUQA4	100000	11/8/2017	9/10/2017	Win
0011I000008SISjQAO	0061I000005YRCHQA4	75000	11/8/2017	9/13/2017	Loss
0011I000008SISkQAO	0061I000005YRC7QAO	25000	11/8/2017	9/17/2017	Active
0011I000008SISlQAO	0061I000005YRCBQA4	75000	11/8/2017	7/27/2017	Active
0011I000008SISlQAO	0061I000005YRCZQA4	50000	11/8/2017	10/16/2017	Win
0011I000008SISlQAO	0061I000005YRCPQA4	5000	11/8/2017	10/18/2017	Win
0011I000008SISmQAO	0061I000005YRCCQA4	120000	11/8/2017	10/20/2017	Active
0011I000008SISmQAO	0061I000005YRCDQA4	100000	11/8/2017	10/27/2017	Win
0011I000008SISmQAO	0061I000005YRCaQAO	85000	11/8/2017	9/29/2017	Active
0011I000008SISmQAO	0061I000005YRCVQA4	50000	11/8/2017	9/12/2017	Win
0011I000008SISnQAO	0061I000005YRCSQA4	85000	11/8/2017	7/31/2017	Active
0011I000008SISnQAO	0061I000005YRCIQA4	35000	11/8/2017	7/30/2017	Active
0011I000008SISnQAO	0061I000005YRC9QAO	5000	11/8/2017	8/1/2017	Win
0011I000008SISoQAO	0061I000005YRCTQA4	150000	11/8/2017	8/9/2017	Win
0011I000008SISoQAO	0061I000005YRCRQA4	20000	11/8/2017	8/2/2017	Active
0011I000008SISoQAO	0061I000005YRCGQA4	10000	11/8/2017	8/5/2017	Win
0011I000008SISrQAO	0061I000005YRCKQA4	150000	11/8/2017	11/1/2017	Win
0011I000008SISrQAO	0061I000005YRCAQA4	85000	11/8/2017	9/8/2017	Win
0011I000008SISrQAO	0061I000005YRCJQA4	20000	11/8/2017	10/29/2017	Active
0011I00000EmeOlQAJ	0061I000007S2RYQA0	100000	12/29/2017	12/31/2017	Active
0011I00000EmeOmQAJ	0061I000007S2RWQA0	15000	12/29/2017	12/31/2017	Active
0011I00000EmeOnQAJ	0061I000007S2RXQA0	120000	12/29/2017	12/31/2017	Active
0011I00000EmeOoQAJ	0061I000007S2pDQAS	120000	12/29/2017	11/27/2017	Active
0011I00000EmeOoQAJ	0061I000007S2p8QAC	85000	12/29/2017	10/31/2017	Active
0011I00000EmeOoQAJ	0061I000007S2pAQAS	50000	12/29/2017	1/9/2018	Active
0011I00000EmeOpQAJ	0061I000007S2p5QAC	50000	12/29/2017	10/13/2017	Active
0011I00000EmeOpQAJ	0061I000007S2p6QAC	25000	12/29/2017	6/9/2017	Win
0011I00000EmeOpQAJ	0061I000007S2p7QAC	5000	12/29/2017	11/16/2017	Active
0011I00000EmeOqQAJ	0061I000007S2p4QAC	150000	12/29/2017	2/1/2016	Loss
0011I00000EmeOqQAJ	0061I000007S2otQAC	120000	12/29/2017	10/13/2017	Active
0011I00000EmeOqQAJ	0061I000007S2owQAC	120000	12/29/2017	6/15/2017	Loss
0011I00000EmeOqQAJ	0061I000007S2p0QAC	5000	12/29/2017	12/21/2017	Active
0011I00000EmeOrQAJ	0061I000007S2ooQAC	85000	12/29/2017	5/30/2017	Win
0011I00000EmeOrQAJ	0061I000007S2oqQAC	50000	12/29/2017	12/30/2015	Loss
0011I00000EmeOsQAJ	0061I000007S2oiQAC	35000	12/29/2017	10/23/2015	Win
0011I00000EmeOsQAJ	0061I000007S2ohQAC	30000	12/29/2017	10/3/2015	Loss
0011I00000EmeOtQAJ	0061I000007S2ogQAC	5000	12/29/2017	11/4/2017	Active
0011I00000EmeOuQAJ	0061I000007S2ocQAC	240000	12/29/2017	2/28/2017	Win
0011I00000EmeOuQAJ	0061I000007S2oYQAS	20000	12/29/2017	7/16/2015	Loss
0011I00000EmeOuQAJ	0061I000007S2obQAC	15000	12/29/2017	11/1/2017	Active
0011I00000EmeOwQAJ	0061I000007S2oLQAS	120000	12/29/2017	2/29/2016	Win
0011I00000EmeOwQAJ	0061I000007S2oMQAS	100000	12/29/2017	2/21/2015	Win
0011I00000EmeOwQAJ	0061I000007S2oFQAS	5000	12/29/2017	6/23/2016	Loss
0011I00000EmeOxQAJ	0061I000007S2oBQAS	150000	12/29/2017	12/18/2015	Loss
0011I00000EmeOxQAJ	0061I000007S2oCQAS	150000	12/29/2017	2/7/2017	Win
0011I00000EmeOyQAJ	0061I000007S2o6QAC	75000	12/29/2017	1/9/2018	Active
0011I00000EmeOyQAJ	0061I000007S2o8QAC	30000	12/29/2017	2/10/2015	Win
0011I00000EmeOyQAJ	0061I000007S2o5QAC	10000	12/29/2017	4/3/2016	Loss
0011I00000EmeOyQAJ	0061I000007S2o7QAC	5000	12/29/2017	12/22/2017	Active

[Anonymous · Accepted solution]

Solution:

https://community.powerbi.com/t5/Desktop/Pareto-Analysis-for-Top-N-account-based-on-closed-Date-Filt...

[v-juanli-msft]

Hi @Anonymous 

Do you like this?

Measure = SUM('Table'[Amount])

Measure 2 = RANKX(ALL('Table'[AccountId]),[Measure],,DESC,Dense)

Measure 3 =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[AccountId] ),
    FILTER (
        ALL ( 'Table'[AccountId] ),
        [Measure 2] <= SELECTEDVALUE ( 'Table 2'[topn] )
    )
)


Measure 4 =
VAR amount1 = [Measure]
VAR total1 =
    CALCULATE ( [Measure], ALL ( 'Table' ) )
RETURN
    CALCULATE ( [Measure], FILTER ( ALL ( 'Table' ), [Measure] >= amount1 ) ) / total1

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

x

[Anonymous]

Thanks for quick response  @v-juanli-msft   

I cant achieve Pareto chart 

can you help to create New Summarized Table, it will contain only Top N accounts details based on parameter selection

I tried but its not working 

Top N Account Table = SUMMARIZECOLUMNS ( 'Opportunity'[AccountId],  "Sales", SUM( Opportunity[Amount] ), 
FILTER(ALL('Opportunity'[AccountId]),[Measure2]<=  SELECTEDVALUE('Table 2'[topn])
)

If I segregated top N account & amount value in the separate table means I can apply the Pareto Chart easily   

[v-juanli-msft]

Hi @Anonymous 

Use [Measure 3] in KPI visual, it would show top n data as expected.

Measure 3 = CALCULATE(DISTINCTCOUNT('Table'[AccountId]),FILTER(ALL('Table'[AccountId]),[Measure 2]<=SELECTEDVALUE('Table 2'[topn])))

Add [measure 5] in the visual level filter of the Pareto Chart(column &line chart)

Measure = SUM('Table'[Amount])

Measure 2 = RANKX(ALL('Table'[AccountId]),[Measure],,DESC,Dense)

Measure 4 = var amount1=[Measure] var total1=CALCULATE([Measure],ALL('Table')) return CALCULATE([Measure],FILTER(ALL('Table'),[Measure]>=amount1))/total1

Measure 5 = IF([Measure 2]<=SELECTEDVALUE('Table 2'[topn]),1,0)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-juanli-msft 

Account was filtered based on Top N account filter, but Pareto Line was not changed 

My Expectation was  Pareto line value also want to be change based on the filtered account value

 

[v-juanli-msft]

Hi @Anonymous 

Igonre previous ones, check the new solution

red is a calculated column, others are measures

measure_of_amount = SUM('Table'[Amount])

this is a caluclated column
Column_rank =
RANKX (
    ALL ( 'Table' ),
    CALCULATE ( SUM ( 'Table'[Amount] ), ALLEXCEPT ( 'Table', 'Table'[AccountId] ) ),
    ,
    DESC,
    DENSE
)


Measure_rank =
RANKX ( ALL ( 'Table'[AccountId] ), [measure_of_amount],, DESC, DENSE )

count_id =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[AccountId] ),
    FILTER (
        ALL ( 'Table'[AccountId] ),
        [Measure_rank] <= SELECTEDVALUE ( 'Table 2'[topn] )
    )
)


flag = IF(MAX([Column_rank])<=SELECTEDVALUE('Table 2'[topn]),1,0)

col_amount = IF([flag]=1,[measure_of_amount])

percent_clc =
IF (
    [flag] = 1,
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Column_rank] <= MAX ( 'Table'[Column_rank] )
                && [Column_rank] <= SELECTEDVALUE ( 'Table 2'[topn] )
        )
    )
        / CALCULATE (
            SUM ( 'Table'[Amount] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                [Column_rank] <= SELECTEDVALUE ( 'Table 2'[topn] )
            )
        )
)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi v-juanli-msft

I have filtered Close Date, again it shows the wrong result  

I have posted my exact requirement in the below URL, Could you please assist me to resolve this issue? 

https://community.powerbi.com/t5/Desktop/Pareto-Analysis-for-Top-N-account-based-on-closed-Date-Filt... 

[v-juanli-msft]

Hi @Anonymous 

If you use "summarized" function to create a calculated table, the value on the chart can't be changed based on the parameter selected.

You could find the measure in the visual level filter from my screenshot,

It will limit the chart to show only top 10 data.

For more details, please download my pbix to find how to create these measures

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.