Forum Discussion

moshe_Arama2022's avatar
moshe_Arama2022
Frequent Visitor
3 years ago

Frequency Pareto

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])...
Need Help
Irwan's avatar
Irwan
Icon for Super User rankSuper User
2 years ago

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.