topic Re: Rank & Rankx for calculated column in calculated table in DAX Commands and Tips

Rank & Rankx for calculated column in calculated table

Amrselim1989 — Tue, 27 Jun 2023 04:56:44 GMT

Dear Guys,

kindly note i have calculated table which is i summerize sales by product name in it, so fine so far

after this i used new column to calculate a column

Rank Net Sales = RANKX ( 'Products Header', [Header Total Sales],, DESC, DENSE )

so fine so far

but the problem starts now, i want to rank the product category depending on total sales for this category and I have to put it in the same calculated table just like below

any help?!

Re: Rank & Rankx for calculated column in calculated table

some_bih — Tue, 27 Jun 2023 05:58:48 GMT

Hi Amrselim1989 your measure to rank sales by category could be

Rank by category=RANKX(ALL(Table1[Category]); CALCULATE(SUM(Table1[Sales]));;DESC)

Adjust Table1 to your table name

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Rank & Rankx for calculated column in calculated table

Amrselim1989 — Tue, 27 Jun 2023 06:18:40 GMT

unfortunately, it doesn't work, it only gives me two values instead of four.

there a screen shot if i make a sperate table for brand and i ranked it

but i can't rank it in the details table based on the summation of brand sales

any help??!

Re: Rank & Rankx for calculated column in calculated table

devanshi — Tue, 27 Jun 2023 06:19:35 GMT

Total Sales = SUM([Sales])

Category Ranks = RANKX(ALL('Table'),[Total Sales],Desc)

Re: Rank & Rankx for calculated column in calculated table

some_bih — Tue, 27 Jun 2023 06:26:00 GMT

Hi Amrselim1989 my solution is measure. Go to your pivot table and put this measure into pivot table and check results.

Re: Rank & Rankx for calculated column in calculated table

Amrselim1989 — Tue, 27 Jun 2023 06:28:15 GMT

No, i don't want it as a measure it must be a calculated column, it must be a calculated column for other purposes

thank you for your patience.

Re: Rank & Rankx for calculated column in calculated table

Amrselim1989 — Tue, 27 Jun 2023 06:39:12 GMT

i found my solution

Rank by category = RANKX(ALL('Products Header'[BRAND_NAME]), CALCULATE(sum('Products Header'[Total Net Sale]),ALLEXCEPT('Products Header','Products Header'[BRAND_NAME])),,DESC,Dense)

thank you