CODEWITHSUNDEEP
powerbidaxpowerquerydata-analysispowerbi-desktop
R_Student
R_Student

Reputation: 789

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?

Upvotes: 2

Views: 392

Answers (2)

Mik
Mik

Reputation: 2103

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

enter image description here

enter image description here

Upvotes: 2

davidebacci
davidebacci

Reputation: 30174

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

Upvotes: 3

Related Questions