Guitarmageddon
Board Regular
- Joined
- Dec 22, 2014
- Messages
- 159
Hello everyone,
Im having a heck of a time getting a rank function within my pivot table to work for my purposes.
I have just one master table this is working off. It is a table of an item assortment going to about 100 locations, and its about 900 or so items. So in other words, REPEATED locations, and REPEATED items, and all their sales data. Just a condensed hypothetical example so we're on the same page:
When I start to pivot this data though, and seek out a rank, it starts to get weird when adding multi-level fields to my pivot. I know Im messing up something with the filter context.
Here is a snip of what I have. Column C is a measure, which is a sum of my field "sold in last 12 mos"
Column D is using the built in "show values as RANK" function of the pivot table, using the measure created above. In other words, this is not from a measure actually calculating a rank, as you probably know. My dilemma is how can I get this using RANKX, when we're dealing with a source table that has a bunch of repeating values such as the items and category descriptors?
Column E is a dax measure, where i was fussing with using "ALL" but that just gave me weird values. My goal was to provide two things here:
1)show a rank on the pivot table WITHIN the parent "category" i.e. here its "TACTICAL HOLSTERS"
2)show a rank on the pivot table in the context of ALL items (what i was driving at with column E)
Can anyone help? How can you employ RANKX when youve got repeated values in a single big flat table?
Im having a heck of a time getting a rank function within my pivot table to work for my purposes.
I have just one master table this is working off. It is a table of an item assortment going to about 100 locations, and its about 900 or so items. So in other words, REPEATED locations, and REPEATED items, and all their sales data. Just a condensed hypothetical example so we're on the same page:
| sku | description | site number | sales units this year |
| 1234 | abc | 101 | 8521 |
| 1234 | abc | 102 | 6325 |
| 456 | ced | 101 | 12 |
| 456 | ced | 102 | 1212 |
| 456 | ced | 103 | 121 |
| 7890 | def | 101 | 8787 |
| 7890 | def | 102 | 1212 |
| 7890 | def | 103 | 1212 |
When I start to pivot this data though, and seek out a rank, it starts to get weird when adding multi-level fields to my pivot. I know Im messing up something with the filter context.
Here is a snip of what I have. Column C is a measure, which is a sum of my field "sold in last 12 mos"
Column D is using the built in "show values as RANK" function of the pivot table, using the measure created above. In other words, this is not from a measure actually calculating a rank, as you probably know. My dilemma is how can I get this using RANKX, when we're dealing with a source table that has a bunch of repeating values such as the items and category descriptors?
Column E is a dax measure, where i was fussing with using "ALL" but that just gave me weird values. My goal was to provide two things here:
1)show a rank on the pivot table WITHIN the parent "category" i.e. here its "TACTICAL HOLSTERS"
2)show a rank on the pivot table in the context of ALL items (what i was driving at with column E)
Can anyone help? How can you employ RANKX when youve got repeated values in a single big flat table?
