Rank of Total Sales within a group and with slicers

[DrBrentRD]

I'm new to DAX and PowerBI, but well versed in Excel and VBA. I've spend several days looking for a solution to my issue and haven't had any success.

I have a number of tables within the data model. The 'Sales' table (see below) has the sales by product for each month. The 'Products' table holds various other details including the country where each product is sold. Each product has a base number (links all the international varieties of a given product). The report allows slicing by the base number, sales dates, and underlying blend (recipe). 

I'm trying to create a table visual (see snipet below) that shows a number of sales details including where each variant (aka international version) ranks within its respective country (e.g. Variant A is the #5 best seller for US, Variant B is the #8 best seller for India, etc.). The following DAX gets me a summary of total sales for all of the products in a given country, but I can't figure out how to rank these values and then pull out the one rank I need for each row in the visual.

SUMMARIZECOLUMNS(tblProducts[ProdID],FILTER(tblSalesUnits,AND(tblSalesUnits[UnitMonth]>=DATEVALUE("Mar-2017") && tblSalesUnits[UnitMonth]<=DATEVALUE("Jun-2018"),RELATED(tblProducts[Country])="US")),"TotalSales",SUM(tblSalesUnits[Units]))

I've hardcoded the dates and country into the above for testing purposes, but the final code will need to pull these values from each row in the visual and the three slicers.

Any help is greatly appreciated.

Thanks,

Brent

[v-yulgu-msft]

Hi @DrBrentRD,

SUMMARIZECOLUMNS(tblProducts[ProdID],FILTER(tblSalesUnits,AND(tblSalesUnits[UnitMonth]>=DATEVALUE("Mar-2017") && tblSalesUnits[UnitMonth]<=DATEVALUE("Jun-2018"),RELATED(tblProducts[Country])="US")),"TotalSales",SUM(tblSalesUnits[Units]))

Above formula returns a calculated table, right? And actually, you want this table show data conditionally in a table visual based on slicer selection, right? If so, you should be aware that calculated table is static once it is initilized, won't be dynamically changed corresponding to slicer. In this scenario, you should create measures  instead to get total sales per country per product.

To rank the total sales per country, you could try this:

rank = RANKX(ALL(Table3[Product]),[Total Sales],,DESC,Dense )

Best regards,

Yuliana Gu

[DrBrentRD]

@v-yulgu-msft Thanks for your reply. 

I do realize the distinction between a calculated table/column and a measure. I'm trying to create a measure that includes a dynamically calculated table (the SUMMARIZECOLUMNS code) in order to dynamically obtain the list of products and their total period (date slicer) sales by country. I just can't seem to get the rank within this dynamically calculated table and then repeat this for each row inside the table visualization.

I've tried to encorporate the pattern you provided into the dynamic table measure above and it returns "1" for all rows.

I'm not sure that I'm doing a good job explaining this. 

Ultimately what I need is a pattern that will take the slicer data (date range, base product #, and blend) and show the rank of each product that meets these criteria within its own country. The table visualization will only show the variants off of the selected base product. In other words, each row of this visualization is a variant of the base product for a different country. Put another way, the visualization has one row per country. More popular products may have many rows, less popular ones may only be sold in 2-3 countries, thus the visualization only has 2-3 rows. However, each country may sell a dozen products (only one of which is in the table visualization). I want to know where this one product ranks within the dozen or so other products (not shown) for this country.

Hopefully that explains things a little better.

Thanks,

Brent