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