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

Question

Hello lovely people of SO,

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

I dont know if there is a way to do this on Power BI but if you guys can help me out I will be so thankful I will be very attentive and diligent to read all of your comments THANKS A MILLION!

user_18345037 · Answer

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

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)

user_19042379 · Answer

How about this, Ron Rosenfeld? )

It's a stepwise measure. So, it's easy to get and idea how it's works. You need 3 equal measures, but don't forget to chage names and TopPos Values for TOP2 and TOP3.

TOP1 = 
VAR TopPos = 1
VAR currentRegion = SELECTEDVALUE('table'[REGION]) 
-- VAR currentRegion = VALUES('table'[REGION]) -- if you want to have a value in total
VAR tblCount =
    ADDCOLUMNS(
        SUMMARIZE('table','table'[PRODUCT],'table'[REGION])
        ,"Count",CALCULATE(CountRows('table'))*1000000
    )
VAR tblCountIndex =
        ADDCOLUMNS(
            tblCount
            ,"NameIndex",RANKX(VALUES('table'[PRODUCT]),[PRODUCT])
        )

VAR filteredByRegion = 
        FILTER(
            tblCountIndex
            ,[REGION]=currentRegion 
          --, [REGION] IN currentRegion --for value in total
        )

VAR tblRanked=
        ADDCOLUMNS(
            filteredByRegion
            ,"Rank",RANKX(filteredByRegion,[Count] + [NameIndex])
        )
VAR TopProduct=
        SELECTCOLUMNS(
            FILTER(
                tblRanked
                ,[Rank]=TopPos
            )
            ,[PRODUCT]
        )
VAR RESULT =
    CALCULATE(
        SELECTEDVALUE('table'[PRODUCT])
        ,'table'[PRODUCT] = TopProduct
        )
RETURN
    RESULT

...

TOP2 = 
    VAR TopPos = 2
...
TOP3 = 
    VAR TopPos = 3

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

2 Answers