powerbidaxpowerquerydata-analysispowerbi-desktop

Using DAX to create a summary table of COUNTS allowing GLOBAL FILTERS to take place

I have the following table:

PRODUCT REGION REP MONTH
REG-YH-67 NORTH JANE JUNE
REG-YH-67 NORTH JANE MARCH
REG-YH-67 NORTH JANE DECEMBER
REG-YH-89 NORTH JANE SEPTEMBER
REG-YH-67 OUT-OF-REG JANE JUNE
REG-YH-9 NORTH JANE MARCH
REG-YH-9 WEST ALEX DECEMBER
REG-YH-9 WEST ALEX SEPTEMBER
REG-YH-30 WEST WILL JUNE
REG-YH-33 WEST WILL MARCH
REG-YH-33 OUT-OF-REG WILL DECEMBER
REG-YH-33 WEST WILL SEPTEMBER
REG-YH-33 WEST MARTHA JUNE
REG-YH-33 WEST MARTHA MARCH
REG-YH-67 OUT-OF-REG JANE DECEMBER
REG-YH-67 OUT-OF-REG JANE SEPTEMBER
REG-YH-9 NORTH JANE JUNE
REG-YH-89 NORTH JANE MARCH
REG-YH-9 NORTH JANE DECEMBER
REG-YH-9 WEST JANE SEPTEMBER
REG-YH-9 OUT-OF-REG JANE JUNE
REG-YH-9 NORTH JANE MARCH
REG-YH-67 OUT-OF-REG JANE DECEMBER
REG-YH-9 NORTH JANE SEPTEMBER
REG-YH-67 OUT-OF-REG JANE JUNE
REG-YH-9 OUT-OF-REG JANE MARCH

This is the source file that I use in a power bi doc that I want to create, I have 2 global filters in my main page which are REP and MONTH, and I want to learn how to use DAX to create a summary table that will plot the top 3 best selling PRODUCTs for each REGION

If we do not use the filters then the desired table would look like this:

REGION BEST SELLING 2ND BEST SELLING 3RD BEST SELLING
NORTH REG-YH-9 REG-YH-67 REG-YH-89
OUT-OF-REG REG-YH-67 REG-YH-9 REG-YH-33
WEST REG-YH-33 REG-YH-9 REG-YH-30

But I want to be able to use my global filters in the PBI dashboard and perhaps filter by REP= MARTHA & JANE so that my summary table would recalculate/turn to be:

REGION BEST SELLING 2ND BEST SELLING 3RD BEST SELLING
NORTH REG-YH-9 REG-YH-67 REG-YH-89
OUT-OF-REG REG-YH-67 REG-YH-9 null
WEST REG-YH-33 REG-YH-9 null

Is there a way to do this on Power BI?

Solution

  • Create 3 measures changing the rank to 1,2 or 3

    enter image description here enter image description here

    Measure = 
    
VAR tbl =
        ADDCOLUMNS(
            'table'
            ,"@rank",RANKX(VALUES('table'[PRODUCT]),CALCULATE( COUNT('table'[PRODUCT]), ALLEXCEPT('table','table'[PRODUCT],'table'[REGION])), , ,Dense)
        )
VAR temp =  SELECTCOLUMNS( FILTER(tbl, [@rank] = 1),"a", 'table'[PRODUCT])
VAR result =    CALCULATE(MAX('table'[PRODUCT]),'table'[PRODUCT] IN temp)
RETURN result

    Below solution accounts for row totals if this is important.

    Measure1 = 
    
VAR tbl1 =
        ADDCOLUMNS(
            'table'
            ,"@rank",RANKX(VALUES('table'[PRODUCT]),CALCULATE( COUNT('table'[PRODUCT]), ALLEXCEPT('table','table'[PRODUCT],'table'[REGION])), , ,Dense)
        )
VAR temp1 =  SELECTCOLUMNS( FILTER(tbl1, [@rank] = 1),"a", 'table'[PRODUCT])
VAR result1 =    CALCULATE(MAX('table'[PRODUCT]),'table'[PRODUCT] IN temp1)

VAR tbl2 =
        ADDCOLUMNS(
            'table'
            ,"@rank",RANKX(VALUES('table'[PRODUCT]),CALCULATE( COUNT('table'[PRODUCT]), ALLEXCEPT('table','table'[PRODUCT])), , ,Dense)
        )
VAR temp2 =  SELECTCOLUMNS( FILTER(tbl2, [@rank] = 1),"a", 'table'[PRODUCT])
VAR result2 =    CALCULATE(MAX('table'[PRODUCT]),'table'[PRODUCT] IN temp2)




RETURN  IF(ISFILTERED('table'[REGION]), result1, result2)

    enter image description here