DAX % of total count if measure qualifies criteria

Question

DAX 2013 standalone power pivot.

I have a sales table with Product and Brand columns, and Sales measure which explicitly sums up sales column.

Task in hand: I need to create 1 measure RANK which would ...

if Product is filtered expressly, then return count of Products that have higher or equal sales amount, divided by total count of products.
If it's a subtotal brand level, show the same but for brands.

My current approach is using RANK and then MAXX of rank which seems working but a no-go - slow nightmare. Excel runs out of memory.

Research: it's been a week. This is the most relevant post i found anywhere, this question here , but it's in MDX.

In my example picture, I'm showing Excel formulas with which I can get to the result. Ideally there shouldn't be any helpers, 1 formula for all.

I.E.

RANK:=IF( HASONEFILTER(PRODUCTS[PRODUCT], HELPER_PROD, HELPER_BRAND)

where HELPER_PROD part would be something like this - need to find a way to refer to "current" result in pivot table like Excel does using [@[...:

HELPER_PROD:=COUNTX(ALL(PRODUCTS), [SALES]>=[@[SALES]]) / COUNTX(ALL(PRODUCTS))
HELPER_BRAND:=COUNTX(
              DISTINCT(ALL(PRODUCTS[BRAND])), 
              [SALES]>=[@[SALES]]) / 
              COUNT(DISTINCT(ALL(PRODUCTS[BRAND]))

WimV WimV · Accepted Answer · 2017-05-02T13:35:25

You can use the "Earlier" function to compare with the current record.

ProductsWithHigherSales:=CALCULATE(countrows(sales),
FILTER(all(Sales),
countrows(filter(Sales,Sales[Sales]<=EARLIER(Sales[Sales]))) 
))

Using Earlier function in measures: can-earlier-be-used-in-dax-measures

Used workbook: Excel File

DAX % of total count if measure qualifies criteria

1 Answers