Frequency Pareto

[moshe_Arama2022]

Hi ,

I try to bulid pareto chart that show the symptoms with the highest frequency

my Rank is :

Rank_Freq = IF(HASONEVALUE(Includes_PM_s[symptom key]) ,RANKX(ALL(Includes_PM_s[symptom key]),Includes_PM_s[Count_Symptom],,DESC,Dense))

How can I separate the calculation for the same Rank ?

Thank's 

[Irwan]

hello @moshe_Arama2022 ,

those same rank happened becaused your Count_Symptom has same value.

Both Abs Encoder Fault and Gantry Synchro have same value of 4 in Count_Symptom.

To separate those same rank, you need to have another value for comparing those those same value (Count_Symptom in you data).

for example: 

I took your Count_Symptom data.

as you can see on 'Rank from Value column', it has exactly same duplicate rank as your data.

then, i made dummy 'Number' column as value comparison. This column will rank those duplicate in 'Value' column.

as you can see on 'Rank toward another column', there is no duplicate rank anymore because if duplicate was found, those duplicate value will be ranked in manner of 'Number' column rank.

here is the DAX for having non duplicate rank:

Rank toward another column =

var _Value = 'Table'[Value]

var _DupeValue = COUNTROWS(FILTER('Table','Table'[Value]=_Value)) //find any duplicate in 'Value' column

Return

RANKX(

    'Table',

    'Table'[Value]+IF(

        _DupeValue>1, //using conditional if for finding any duplicate in 'Value' column

        RANKX(

            FILTER(

                'Table',

                'Table'[Number]>EARLIER('Table'[Number] //this line will rank 'Number' Column

            )

        ),

        'Table'[Number]

        ,,

        DESC,

        Dense

        )

    )

)

However, this is just one solution. Other power bi expert might have better solution.

Hope this will help you.

Thank you.