Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Hi,
You may refer to my solution in this file. I have not used any calculated column - only meaures.
Hope this helps.
Hi,
Share a dataset and show the expected result.
.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.
Hi,
You may refer to my solution in this file. I have not used any calculated column - only meaures.
Hope this helps.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
96 | |
80 | |
67 | |
62 |
User | Count |
---|---|
137 | |
106 | |
104 | |
81 | |
63 |