summarize rows based on a row specific measure like rankx
This is what I need: The sales amount % of items in the top 20 against the total sales. This must be dynamic, the top20 this week is different from the top20 last week. So I create a measure: [sales rank] = RANKX(ALLSELECTED('DIM-Item'),[Sales amt.]) this gets all items ranked based on the applied filters like week or item category. Now I want to calculate the sales amount of the items with rank < 21 SUMX( FILTER('DIM-Item', [sales rank] < 21), [Sales amt.]) So far so good. Only items in the top20 (rank < 21) get a value in the table. The problem is the total line. The total line show the full sales amount. This is because the [sales rank] in the total line = 1. It does not filter the item lines and then summarizes. It calculates the formula on the total line and then the filter for [sales rank] < 21 is not correct. Please check this table, especially the total line: Thanks for the help Kind regards, Johan
Stuck on %of Total Problem
I am working with grouping previous year customer sales into small, medium, and large buckets. A Customer size table has been created for the grouping: With a page filter for small, medium, and large sales can be displayed for the selected size. The dax formula to calculate sales is: CY Customer Sales by Grouping 4 Test = Calculate([CY Total Sales], filter(values(Sales[Sold-To Party]), countrows( Filter('Customer Size Table', [PY Total Sales]>='Customer Size Table'[Min]&& [PY Total Sales]<= 'Customer Size Table'[Max]))>0)) The problem that I am having is that I am trying to write a DAX for % of Total sales which compares the size selected to ALL sizes. The formula that I have is below but is not working. I understand that the size filter has to be removed from one portion of the DAX but the solution is escaping me. This does not work: % of Total Customer Size Test 4 = divide([CY Customer Sales by Grouping 4 Test],calculate([CY Customer Sales by Grouping 4 Test]),all('Customer Size Table'[Size])) Any help would be greatly appreciated.
