HELP_POWERPIVOT DAX RANKX Function

G

_Google

New Member
Joined
Feb 6, 2013
Messages
40
Hi

I need some help for issue with RANKX function with DAX. (I tried learn it from net, but when i populate in my data table, it does not work.

- My data source : I have only one source table in the model.
- I need Rank of Product in one column and Rank of Product with Material in another column.

Function are Rank Total Profit by Product SUM:=RANKX(ALL(fUnits[Product]),CALCULATE(SUM(fUnits[Profit]))) Rank Total Profit by Product within Manuf:=IF(HASONEVALUE(fUnits[Product]),RANKX(ALLEXCEPT(fUnits,fUnits[Product]),CALCULATE(SUM(fUnits[Profit]))))



Report view [TABLE="width: 675"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Manufacturer[/TD]
[TD]Product[/TD]
[TD]Total Profit[/TD]
[TD]Rank Total Profit by Product SUM[/TD]
[TD]#Rank Total Profit by Product within Manuf[/TD]
[/TR]
[TR]
[TD]Bower Booms[/TD]
[TD]3 MTA[/TD]
[TD="align: right"]$142,960.18[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Bower Booms[/TD]
[TD]Aussie Quad[/TD]
[TD="align: right"]$131,252.83[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Channel Craft[/TD]
[TD]Kangaroo[/TD]
[TD="align: right"]$135,467.69[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Channel Craft[/TD]
[TD]Omega[/TD]
[TD="align: right"]$125,677.40[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Channel Craft[/TD]
[TD]V Rang[/TD]
[TD="align: right"]$82,626.53[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Colorado Boomerangs[/TD]
[TD]Aspen[/TD]
[TD="align: right"]$288,120.81[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Colorado Boomerangs[/TD]
[TD]Crested Beaut[/TD]
[TD="align: right"]$225,621.64[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Colorado Boomerangs[/TD]
[TD]Yanaki[/TD]
[TD="align: right"]$164,658.41[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Gel Boomerangs[/TD]
[TD]Bellen[/TD]
[TD="align: right"]$273,704.07[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Gel Boomerangs[/TD]
[TD]Quad[/TD]
[TD="align: right"]$495,972.38[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Gel Boomerangs[/TD]
[TD]Sunshine[/TD]
[TD="align: right"]$194,931.77[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Manu Rangs[/TD]
[TD]Manu Distance[/TD]
[TD="align: right"]$208,751.21[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Manu Rangs[/TD]
[TD]Manu MTA[/TD]
[TD="align: right"]$203,058.35[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Turning Point[/TD]
[TD]Darnell Fast Catch[/TD]
[TD="align: right"]$361,857.45[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Turning Point[/TD]
[TD]Darnell V[/TD]
[TD="align: right"]$187,139.27[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Turning Point[/TD]
[TD]Try Fly[/TD]
[TD="align: right"]$82,962.20[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD][/TD]
[TD="align: right"]$3,304,762.19[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Data Table Source : This is only sample hence total any different from report view
[TABLE="width: 563"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Product[/TD]
[TD]Units[/TD]
[TD]Profit[/TD]
[TD]Manufacturer[/TD]
[/TR]
[TR]
[TD="align: right"]01/09/2017[/TD]
[TD]Sunshine[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]235.52[/TD]
[TD]Gel Boomerangs[/TD]
[/TR]
[TR]
[TD="align: right"]28/02/2017[/TD]
[TD]Yanaki[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]58.68[/TD]
[TD]Colorado Boomerangs[/TD]
[/TR]
[TR]
[TD="align: right"]27/10/2017[/TD]
[TD]Darnell Fast Catch[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]32.19[/TD]
[TD]Turning Point[/TD]
[/TR]
[TR]
[TD="align: right"]27/09/2017[/TD]
[TD]Try Fly[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]16.32[/TD]
[TD]Turning Point[/TD]
[/TR]
[TR]
[TD="align: right"]21/07/2017[/TD]
[TD]Aussie Quad[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]128.45[/TD]
[TD]Bower Booms[/TD]
[/TR]
[TR]
[TD="align: right"]06/08/2017[/TD]
[TD]Yanaki[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]25.15[/TD]
[TD]Colorado Boomerangs[/TD]
[/TR]
[TR]
[TD="align: right"]08/04/2017[/TD]
[TD]Bellen[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]18.46[/TD]
[TD]Gel Boomerangs[/TD]
[/TR]
[TR]
[TD="align: right"]27/05/2017[/TD]
[TD]Yanaki[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]16.77[/TD]
[TD]Colorado Boomerangs[/TD]
[/TR]
[TR]
[TD="align: right"]17/09/2017[/TD]
[TD]Quad[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]290.07[/TD]
[TD]Gel Boomerangs[/TD]
[/TR]
[TR]
[TD="align: right"]28/11/2017[/TD]
[TD]V Rang[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]104.77[/TD]
[TD]Channel Craft[/TD]
[/TR]
[TR]
[TD="align: right"]05/02/2017[/TD]
[TD]Manu Distance[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]72.91[/TD]
[TD]Manu Rangs[/TD]
[/TR]
[TR]
[TD="align: right"]23/08/2017[/TD]
[TD]Crested Beaut[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]361.11[/TD]
[TD]Colorado Boomerangs[/TD]
[/TR]
[TR]
[TD="align: right"]03/07/2017[/TD]
[TD]Darnell V[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13.26[/TD]
[TD]Turning Point[/TD]
[/TR]
[TR]
[TD="align: right"]10/08/2017[/TD]
[TD]Sunshine[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]16.24[/TD]
[TD]Gel Boomerangs[/TD]
[/TR]
[TR]
[TD="align: right"]11/06/2017[/TD]
[TD]Try Fly[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]16.32[/TD]
[TD]Turning Point[/TD]
[/TR]
[TR]
[TD="align: right"]05/07/2017[/TD]
[TD]Aussie Quad[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]64.22[/TD]
[TD]Bower Booms[/TD]
[/TR]
[TR]
[TD="align: right"]06/09/2017[/TD]
[TD]Darnell V[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]190.12[/TD]
[TD]Turning Point[/TD]
[/TR]
[TR]
[TD="align: right"]04/01/2017[/TD]
[TD]Bellen[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]27.69[/TD]
[TD]Gel Boomerangs[/TD]
[/TR]
[TR]
[TD="align: right"]18/05/2017[/TD]
[TD]Sunshine[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]56.85[/TD]
[TD]Gel Boomerangs[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
You must log in or register to reply here.

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top