Rank for running total - measure vs. column

[RMDNA]

Hi all,

Note: I'm working on a PBIX to share, but the amount of data to simulate, even for a subset, will take me a while.

I'm trying to create a rank of products by sales volume for my product table, which will eventually be used for a running total. From what I understand, the rank needs to be a column to be used in the running total formula. When I was only using slicers from the products table, I could create the rank as a column, and it worked just fine.

Rank = rankx(all('Product Sales'), 'Product Sales'[% of Total])

Running Total = 
CALCULATE (
    SUM ('Product Sales'[% of Total]),
    FILTER (
        ALL ('Product Sales'),
		'Product Sales'[Rank] <= EARLIER ('Product Sales'[Rank])
    ))

However, when I attempt to filter by fields outside of the home table, Rank as a column returns all values as 1. I can get it to work as a measure, but then it can't be used as a running total.

I feel like I'm missing something simple here.

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.

[RMDNA]

@Ashish_Mathur,

.PBIX is attached.

I have a visual on the first tab which is working properly - products are arranged in decreasing order by sales volume and rank (and by association, increasing running total). It properly responds to the slicer on the page.

The second tab contains a table with the same data, but I can't get the rank to work as a column (in order to work in the running total formula) and still respond to the slicer. @Greg_Deckler recommended "a temp table using something like SUMMARIZE or GROUPBY," but I'm not sure exactly what he means, or how to accomplish it.

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution in this file.  I have not used any calculated column - only meaures.

Hope this helps.

[Greg_Deckler]

Why can't you use it as a running total? Just create a temp table using something like SUMMARIZE or GROUPBY or FILTER or whatever. Can't be more specific without sample/example data.

[RMDNA]

@Greg_Deckler,

I don't know enough to do what you're suggesting. PM'd you data.