Power BI Rank within Matrix Visual

Question

I've spent many weeks trying to get this to work including a posting at another site. No luck. Not sure what I'm missing.

The following code gets me the table of data that I need. However, I want just the "rank" value and I want it for each row within a matrix visual.

First some background, the PowerBI page has slicers on dates and "base" product. Any given product may be sold in multiple countries. All carry the same base product suffix with a country-specific prefix.

The matrix in question displays several aspects of all the product variants for the selected base product. I want to show where each variant ranks (unit sales) within its country.

VAR tblCountrySales =
    SUMMARIZE (
        FILTER(
            tblSalesUnits,
            RELATED(tblProducts[Country])="US"
        ),
        tblProducts[ProdID],
        "Total Sales", SUM ( tblSalesUnits[Units] )
    )
RETURN
    ADDCOLUMNS (
        tblCountrySales,
        "ProductRank", RANKX ( tblCountrySales, [Total Sales] )
    )

Is there a way to either pull just the rank for a single product from the above code, or, better yet, a DAX pattern that will get me the rank by unit sales of each product variant within its respective country?

I've use these statements to successfully get the product ID and country for each row within the visual.

VAR ProdSales = SUM(tblSales[Units])
VAR ProdCountry = SELECTEDVALUE(tblProducts[Country])

Any help is greatly appreciated. I assume I'm missing something with row context within the visual as a simple COUNTROWS on the table expressions above always returns "1". Being new to PowerBI and DAX I'm still trying to grasp the nuances of filter and row contexts.

The products table contains data like...
ProdID.....BaseID....Name...Country
7190...........7190.....xxxx.....US
150207190......7190.....XXXX....Panama
241807190......7190.....xxxx.....Spain

The sales table contains data like...
ProdID......SalesMonth......Units.....USD
7190........July 2010.......4563....$23491.00
150207190...July 2010.......2543....$16235.00
241807190...July 2010.......1263....$8125.00

There is a dates table as well that links the SalesMonth to the usual selection of date display formats. The two tables above are linked via ProdID, though the visuals are sliced by the BaseID. I'd attach a picture, but don't have permissions to do so.

It's very difficult to help without some example data to work with. You are much more likely to get an answer if you have an mcve. — Alexis Olson

Brent Brent · Accepted Answer · 2018-10-29T15:41:49

With some more tinkering I found this solution.

Country Rank = 
VAR ProdSales = SUM(tblSales[Units])
VAR ProdCountry = SELECTEDVALUE(tblProducts[Country])
VAR tblCountrySales =
    SUMMARIZE (
        FILTER(
            ALLEXCEPT(tblProducts,tblProducts[BaseID]),
            tblProducts[Country]=ProdCountry
        ),
        tblProducts[ProdID],
        "Total Sales", SUM ( tblSales[Units] )
    )
RETURN
    IF(ProdSales>0,COUNTROWS(FILTER(tblCountrySales,[Total Sales]>ProdSales))+1,BLANK())

Power BI Rank within Matrix Visual

1 Answers