Rank wise Cumulative Frequency of Calculate Percentage based on Column total

[harib]

Hello Friends

I need Rank wise Cumulative Frequency of Calculate Percentage based on Column total 

I have a data like below image and i want Each Product Percentage (%Products) of Total products (#Products) and also needs cumulative frequency (%Total) of Each Product Percentage (%Products) with Rank

I have followed below steps. I got output like 1st image , but if i dont give rank range in filter output is perfect, when i apply rank range in filter, (%Total) not getting changing according to (%Products) Values. Check it 2nd image 

• Rank = RANKX(ALLSELECTED('Table'[Values]),[Products])
• #Products = COUNT('Table'[Values])
• % Products  (#Products Measure taken and made it as SHow value as Percent of grand total)
• Products Running total = CALCULATE(#Products ,TOPN([Rank],ALL('Table'[Values]),[#Products ]))
• % Total = DIVIDE([Products Running total],CALCULATE([#Products ],ALL('Table'[Values])))

Appreciate if anyone can give solution

Thanks in Advance

[AiolosZhao]

Hi @harib ,

I found a way to do this, please see below photo:

New Column:

RANK1 = RANKX(ALL(Table1),[# Products])

New Measure:

# Products = SUM(Table1[Products])

Measure = CALCULATE([# Products],FILTER(ALL(Table1[RANK1],Table1[Values]),Table1[RANK1] <= MAX(Table1[RANK1])))

Show the percentage for Measure in the table.

Please try.

Aiolos Zhao

[harib]

@AiolosZhao 

HI Aiolos Zhao

Can u share the Sample PBIX file. I'm trying to get it, but rank field showing 1 for all.

Seems your logic looks good.

Thanks

[AiolosZhao]

Hi @harib ,

It seems that not all member can upload the pbix on the community.

I think if you also use the all(table), you won't get the 1 for all.

And you need to use RANKX(all(table),a measure name which calculate sum(product))

You can not use the sum() into rankx directly.

If it's also wrong ,could you please share your expression?

thanks.

Aiolos Zhao

[harib]

@AiolosZhao 

HI Aiolos Zhao

I have followed the steps which u have provided, but Rank columns showing error like below image. 

Then i have created Rank in measure . It's working fine, but when i try to create Measure  it's not taking the Rank measure.see below image

Kindly give me the solution

Thanks

[AiolosZhao]

Seems it shows "A circular depandency was detected : Table Column",

do you have a column named "Column"? if yes, please delete it then try again.

Aiolos Zhao